﻿using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.Print;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using Coldairarrow.Util.ClassLibrary;
using Coldairarrow.Util.Helper;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_CirculationBillBusiness : BaseBusiness<PB_CirculationBill>, IPB_CirculationBillBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        readonly IOperatorDept _opdept;
        public PB_CirculationBillBusiness(IDbAccessor db, IOperator @operator, IOperatorDept @opdept)
            : base(db)
        {
            _operator = @operator;
            _opdept = @opdept;
        }

        #region 外部接口

        public async Task<PageResult<PB_CirculationBill>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_CirculationBill>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_CirculationBill, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PB_CirculationBill> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(PB_CirculationBill data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(PB_CirculationBill data)
        {
            await UpdateAsync(data);
        }

        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }

        public async Task<PageResult<PB_CirculationBillListDto>> GetDataCirculationBillList(PageInput<PB_CirculationBillListConditionDTO> input)
        {
            Expression<Func<PB_CirculationBill, Base_Department, Base_Device, PB_ProductionOrder, PB_Tray, PB_Material, Base_User, Base_User, Base_User, PB_ProcecureSub, Base_User,PB_Material, PB_CirculationBillListDto>> select = (a, c, d, e, f, g, x, m, n, v, k,l) => new PB_CirculationBillListDto
            {

                DeptIdName = c.Name,
                DeviceIdName = d.Name,
                TaskIdName = e.POName,
                TrayId = a.TrayId,
                TrayCode = f.Code,
                TrayIdName = f.Name,
                MaterialIdName = g.Name,
                WorkerIdName = x.RealName,
                StatisticianName = m.RealName,
                ReceiverName = n.RealName,
                ProcedureIdName = v.Name,
                QualityName = k.RealName,
                SubordinateProductName = l.Name,
            };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join c in Db.GetIQueryable<Base_Department>() on a.DeptId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_Device>() on a.DeviceId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_ProductionOrder>() on a.TaskId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Tray>() on a.TrayId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_Material>() on a.MaterialId equals g.Id into ag
                    from g in ag.DefaultIfEmpty()
                    join x in Db.GetIQueryable<Base_User>() on a.WorkerId equals x.Id into ax
                    from x in ax.DefaultIfEmpty()
                    join m in Db.GetIQueryable<Base_User>() on a.Statistician equals m.Id into am
                    from m in am.DefaultIfEmpty()
                    join n in Db.GetIQueryable<Base_User>() on a.Receiver equals n.Id into an
                    from n in an.DefaultIfEmpty()
                    join v in Db.GetIQueryable<PB_ProcecureSub>() on a.ProcedureId equals v.Id into av
                    from v in av.DefaultIfEmpty()
                    join k in Db.GetIQueryable<Base_User>() on a.QualityUserId equals k.Id into ak
                    from k in ak.DefaultIfEmpty()
                    join l in Db.GetIQueryable<PB_Material>() on a.SubordinateProduct equals l.Id into al
                    from l in al.DefaultIfEmpty()
                    select @select.Invoke(a, c, d, e, f, g, x, m, n, v, k,l);

            if (!input.Search.MaterialIdName.IsNullOrEmpty())
            {
                q = q.Where(x => x.MaterialIdName.Contains(input.Search.MaterialIdName));
            }
            if (!input.Search.WorkerIdName.IsNullOrEmpty())
            {
                q = q.Where(x => x.WorkerIdName.Contains(input.Search.WorkerIdName) || x.StatisticianName.Contains(input.Search.WorkerIdName));
            }
            if (!input.Search.StartDate.IsNullOrEmpty() && !input.Search.EndDate.IsNullOrEmpty())
            {
                DateTime StartDate = Convert.ToDateTime(Convert.ToDateTime(input.Search.StartDate).Date.ToString());
                DateTime EndDate = Convert.ToDateTime(Convert.ToDateTime(input.Search.EndDate).Date.ToString()).AddDays(1).AddSeconds(-1);

                q = q.Where(x => StartDate <= x.SendTime && x.SendTime <= EndDate);

            }
            if (!input.Search.Id.IsNullOrEmpty())
            {
                q = q.Where(x => x.Id.Contains(input.Search.Id));
            }
            if (!input.Search.ProcedureId.IsNullOrEmpty())
            {
                q = q.Where(m => m.ProcedureId == input.Search.ProcedureId);
            }
            if (!input.Search.WorkerIdNames.IsNullOrEmpty()) {
                q = q.Where(m=>m.WorkerIdName.Contains(input.Search.WorkerIdNames));
            }
            q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.GetPageResultAsync(input);
        }
        /// <summary>
        /// 半成品入库流水
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_CirculationBillDetailDTO>> GetDataDTOListAsync(PageInput<PB_CirculationBillConditionDTO> input)
        {
            string str = @"select  h.InStorTime as SendTime,b.id as TaskCode ,b.POName as TaskName,d.code as Productcode,d.name as ProductName,d.Spec ,f.name as SubordinateProductName,c.name as ProcedureName, g.InNum  WorkReportQuantity ,e.RealName as creator from PB_CirculationBill a left join PB_ProductionOrder b on a.TaskId=b.Id
            left join PB_ProcecureSub c on a.ProcedureId=c.id
            left join PB_Material d on a.MaterialId=d.Id
            left join Base_User e on a.CreatorId=e.id
            left join PB_Material f on a.SubordinateProduct=f.Id 
						left join TD_RecDetail as g on a.Id=g.CirBillId	and a.MaterialId=g.MaterialId	
						left join TD_InStorage h on g.RecId=h.RecId
						where 1=1 and a.NoticeId is not null and g.InNum!=0 ";
            if (!input.Search.TaskCode.IsNullOrEmpty())
            {
                str += " and b.id like '%" + input.Search.TaskCode + "%' ";
            }
            if (!input.Search.TaskName.IsNullOrEmpty())
            {
                str += " and b.POName like '%" + input.Search.TaskName + "%' ";
            }
            if (!input.Search.ProductCode.IsNullOrEmpty())
            {
                str += " and d.code like '%" + input.Search.ProductCode + "%' ";
            }
            if (!input.Search.ProductName.IsNullOrEmpty())
            {
                str += " and d.name like '%" + input.Search.ProductName + "%' ";
            }
            if (!input.Search.Spec.IsNullOrEmpty())
            {
                str += " and d.spec like '%" + input.Search.Spec + "%' ";
            }
            if (!input.Search.SubordinateProductName.IsNullOrEmpty())
            {
                str += " and f.name like '%" + input.Search.SubordinateProductName + "%' ";
            }
            if (!input.Search.ProcedureName.IsNullOrEmpty())
            {
                str += " and c.name like '%" + input.Search.ProcedureName + "%' ";
            }
            if (!input.Search.StartDate.IsNullOrEmpty() && !input.Search.EndDate.IsNullOrEmpty())
            {
                str += " and DATE_FORMAT(a.SendTime,'%Y-%m-%d')<='" + input.Search.StartDate + "' and DATE_FORMAT(a.SendTime,'%Y-%m-%d')>='" + input.Search.EndDate + "' ";
            }
            if (!input.Search.Creator.IsNullOrEmpty())
            {
                str += " and e.RealName like '%" + input.Search.Creator + "%' ";
            }
            str += " ORDER BY h.InStorTime desc ";
            var lidata = await Db.GetListBySqlAsync<PB_CirculationBillDetailDTO>(str);
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<PB_CirculationBillDetailDTO> { Data = list, Total = num };
        }

        /// <summary>
        /// 半成品入库余量
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_CirculationBillSurplusDTO>> GetDataSurplusDTOListAsync(PageInput<PB_CirculationBillSurplusConditionDTO> input)
        {
            string str = @"select  d.code as MaterialCode,d.name as MaterialName,d.spec as  MaterialSpec,e.`Code` as TrayName,  ifnull(a.ReceivingQuantity,0)-ifnull(a.ReadyNums,0) as Nums,  a.ReceivingQuantity,a.ReadyNums  from PB_CirculationBill a 
            left join PB_Material d on a.MaterialId=d.Id
            left join PB_Tray e on a.TrayId=e.id where  ifnull(a.ReceivingQuantity,0)-ifnull(a.ReadyNums,0)<>0 ";
            if (!input.Search.MaterialCode.IsNullOrEmpty())
            {
                str += " and d.code like '%" + input.Search.MaterialCode + "%' ";
            }
            if (!input.Search.MaterialName.IsNullOrEmpty())
            {
                str += " and d.name like '%" + input.Search.MaterialName + "%' ";
            }
            if (!input.Search.MaterialSpec.IsNullOrEmpty())
            {
                str += " and d.spec like '%" + input.Search.MaterialSpec + "%' ";
            }
            if (!input.Search.TrayName.IsNullOrEmpty())
            {
                str += " and f.name like '%" + input.Search.TrayName + "%' ";
            }
            str += " order by "+input.SortField+"  "+input.SortType+" ";
            var lidata = await Db.GetListBySqlAsync<PB_CirculationBillSurplusDTO>(str);
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<PB_CirculationBillSurplusDTO> { Data = list, Total = num };
        }
        /// <summary>
        /// 获取喷涂作业中列表
        /// </summary>
        /// <param name="DeviceId"></param>
        /// <returns></returns>
        public async Task<List<CirculationBillList>> GetWorkingListAsync(string DeviceId)
        {
            //获取一条本日的作业
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("ROW_NUMBER() over ( ORDER BY a.ReportTime DESC ) AS RowNum,");
            sql.Append("DATE_FORMAT( a.ReportTime, '%Y-%m-%d  %H:%i:%S' ) AS SendTime,");
            sql.Append("a.MaterialId,");
            sql.Append("b.`Name` AS MaterialName,");
            sql.Append("a.Nums,");
            sql.Append("a.DeviceId,");
            sql.Append("d.`Name` AS DeviceName,");
            sql.Append("b.ProductCode MaterialCode,");
            sql.Append("b.Spec AS MaterialSpec ");
            sql.Append("FROM ");
            sql.Append("PB_JobBooking AS a ");
            sql.Append("JOIN PB_Material AS b ON a.MaterialId = b.Id ");
            sql.Append("JOIN Base_User AS c ON a.ReportUserId = c.Id ");
            sql.Append("JOIN Base_Device AS d ON a.DeviceId = d.Id ");
            sql.Append("JOIN PB_CirculationBill as e on a.CirBillId=e.Id ");
            sql.Append("WHERE ");
            sql.Append("a.DeviceId='" + DeviceId + "' ");
            sql.Append("AND ");
            sql.Append("STR_TO_DATE(a.ReportTime, '%Y-%m-%d') = STR_TO_DATE(NOW(), '%Y-%m-%d') ");
            sql.Append("ORDER BY ");
            sql.Append("a.ReportTime desc ");
            sql.Append("LIMIT 10");
            var list = await Db.GetListBySqlAsync<CirculationBillList>(sql.ToString());
            return list;
        }

        /// <summary>
        /// 生产计划执行A1,A4车间
        /// </summary>
        /// <param name="data">参数 time时间 DepartmentId部门</param>
        /// <returns></returns>
        public async Task<List<ProductionPlanExecutionDto>> GetProductionPlaList(ParameterData data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("a.ProcedureId, ");
            sql.Append("b.NAME, ");
            sql.Append("a.DeptId,");
            sql.Append("IFNULL( SUM(  c.Nums ), 0 ) TaskQuantity, ");
            sql.Append("IFNULL( SUM( a.WorkReportQuantity ), 0 ) WorkReportQuantity, ");
            sql.Append("IFNULL( SUM( a.ReceivingQuantity ), 0 ) ReceivingQuantity, ");
            sql.Append("round( SUM( a.ReceivingQuantity ) / SUM( a.WorkReportQuantity ) * 100, 2 ) AS QualifiedRate, ");
            sql.Append("b.Id AS Bid ");
            sql.Append("FROM ");
            sql.Append("PB_CirculationBill AS a ");
            sql.Append("INNER JOIN PB_ProcecureSub AS b ON a.ProcedureId = b.Id ");
            sql.Append("INNER JOIN PB_DispatchDetail AS c ON a.DispatchId = c.ParentId  ");
            sql.Append("AND a.MaterialId = c.MaterialId  ");
            sql.Append("WHERE 1=1 ");
            if (!data.Days.IsNullOrEmpty())
            {
                sql.Append("AND DATE_SUB( CURDATE(), INTERVAL " + data.Days + " DAY ) = DATE_FORMAT( a.CreateTime, '%Y-%m-%d' )  ");
            }
            if (!data.DepartmentId.IsNullOrEmpty())
            {
                sql.Append("AND a.DeptId = '" + data.DepartmentId + "'  ");
            }
            if (!data.ArrayList.IsNullOrEmpty())
            {
                sql.Append("AND b.`Name` IN(" + data.ArrayList + ") ");
            }
            sql.Append("GROUP BY b.Id,a.DeptId  ");
            string str = sql.ToString();
            List<ProductionPlanExecutionDto> List = await Db.GetListBySqlAsync<ProductionPlanExecutionDto>(sql.ToString());


            foreach (var item in List)
            {
                item.QualifiedRate = item.QualifiedRate + "%";
            }
            return List;

        }
        /// <summary>
        /// 大屏显示指定车间当日是否有报工
        /// </summary>
        /// <param name="WorkShopName">车间名称,比如"A1车间","A4车间"</param>
        /// <param name="Day">前第几天的当日</param>
        /// <returns>当日各工序派工合格率</returns>
        public ScreenDisplayValue3DTO GetHaveData(string WorkShopName, int Day)
        {
            string strSQL = @"SELECT IF(SUM(a.data) IS NULL OR SUM(a.Data)=0,'暂无数据','') AS value
FROM
(SELECT COUNT(1) AS data
FROM PB_CirculationBill cb
JOIN Base_Department d ON cb.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%' AND cb.Type=1 AND TO_DAYS(NOW())-TO_DAYS(cb.SendTime)=" + Day.ToString() + @"
UNION
SELECT COUNT(1) AS data
FROM PB_JobBooking jb
JOIN Base_Department d ON jb.WorkshopId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%' AND jb.JobType=1 AND jb.OldOrNew=1 AND TO_DAYS(CURDATE())-TO_DAYS(jb.ReportTime)=" + Day.ToString() + @") a";

            var res = Db.GetListBySqlAsync<ScreenDisplayValue3DTO>(strSQL).Result.FirstOrDefault();
            return res;
        }
        /// <summary>
        /// 大屏显示当日各工序派工合格率
        /// </summary>
        /// <param name="WorkShopName">车间名称,比如"A1车间","A4车间"</param>
        /// <param name="Day">前第几天的当日</param>
        /// <returns>当日各工序派工合格率</returns>
        public async Task<List<PB_DispatchDetailPassRateDTO>> GetTodayPassRateByNameAsync(string WorkShopName, int Day)
        {
            //报工查询视图,流转单的TaskQuantity就是派工数，上件报工的cirllId对应的流转单的TaskQuantity
            //            string strView = @"select `T`.`ReportTime` AS `ReportTime`,`T`.`ReportUserName` AS `ReportUserName`,`T`.`UserId` AS `UserId`,`T`.`ProcecureSubName` AS `ProcecureSubName`,`T`.`MaterialCode` AS `MaterialCode`,`T`.`MaterialName` AS `MaterialName`,`T`.`TaskQuantity` AS `TaskQuantity`,`T`.`Nums` AS `Nums`,`T`.`Completedquantity` AS `Completedquantity`,`T`.`ReturnNums` AS `ReturnNums`,`T`.`DeptId` AS `DeptId`,`T`.`Id` AS `Id` 
            //from
            //(select `jobbooking`.`Id` AS `Id`,`jobbooking`.`ReportTime` AS `ReportTime`,`jobbooking`.`DeptId` AS `DeptId`,`baseuser`.`RealName` AS `ReportUserName`,`baseuser`.`Id` AS `UserId`,`sub`.`Name` AS `ProcecureSubName`,`material`.`Code` AS `MaterialCode`,`material`.`Name` AS `MaterialName`,`bill`.`TaskQuantity` AS `TaskQuantity`,`jobbooking`.`Nums` AS `Nums`,`jobbooking`.`ReturnNums` AS `Completedquantity`,`jobbooking`.`ReturnNums` AS `ReturnNums` 
            //from((((((`PB_JobBooking` `jobbooking` 
            //left join `Base_Device` `device` on((`jobbooking`.`DeviceId` = `device`.`Id`)))
            //left join `PB_ProcecureSub` `sub` on((`device`.`ProcecureSubId` = `sub`.`Id`))) 
            //left join `PB_ProductionOrderDetail` `detail` on((`jobbooking`.`ParentId` = -(`detail`.`Id`)))) 
            //left join `PB_Material` `material` on((`jobbooking`.`MaterialId` = `material`.`Id`))) 
            //left join `Base_User` `baseuser` on((`jobbooking`.`ReportUserId` = `baseuser`.`Id`))) 
            //left join `PB_CirculationBill` `bill` on((`bill`.`Id` = `jobbooking`.`CirBillId`)))
            //union all
            //select `bill`.`Id` AS `Id`,`bill`.`SendTime` AS `ReportTime`,`bill`.`DeptId` AS `DeptId`,`baseuser`.`RealName` AS `ReportUserName`,`baseuser`.`Id` AS `UserId`,`sub`.`Name` AS `ProcecureSubName`,`material`.`Code` AS `MaterialCode`,`material`.`Name` AS `MaterialName`,`bill`.`TaskQuantity` AS `TaskQuantity`,`bill`.`WorkReportQuantity` AS `Nums`,`bill`.`StatisticalQuantity` AS `Completedquantity`,`bill`.`StatisticalQuantity` AS `ReturnNums` 
            //from (((`PB_CirculationBill` `bill` left join `Base_User` `baseuser` on((`bill`.`WorkerId` = `baseuser`.`Id`))) left join `PB_ProcecureSub` `sub` on((`bill`.`ProcedureId` = `sub`.`Id`))) 
            //left join `PB_Material` `material` on((`bill`.`MaterialId` = `material`.`Id`)))) `T`
            // order by `T`.`ReportTime`,`T`.`ProcecureSubName` desc";
            //            string strSQL = @"SELECT IFNULL(plan.`Name`,'未知') AS Name,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,IFNULL(finish.QualifiedRate,0) AS QualifiedRate,
            //IF(finish.Nums>plan.TaskQuantity,100,IFNULL(finish.Nums,0)/plan.TaskQuantity*100) AS FinishedRate 
            //FROM
            //(SELECT ps.`Name`,SUM(dd.Nums) AS TaskQuantity
            //FROM PB_DispatchDetail dd
            //JOIN PB_Dispatch dp ON dd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)="+Day.ToString()+@"
            //LEFT JOIN Base_Department dt ON dp.DeptId=dt.Id AND dt.`Name` LIKE '%"+WorkShopName+@"%'
            //LEFT JOIN PB_ProcecureSub ps ON dd.SubId=ps.Id
            //GROUP BY ps.Id) plan
            //LEFT JOIN
            //(SELECT IFNULL(ProcecureSubName,'未知') AS Name,SUM(TaskQuantity) AS TaskQuantity,SUM(Nums) AS Nums,SUM(Completedquantity) AS FinishedQuantity,
            //IF(SUM(ReturnNums)>SUM(Nums),100,SUM(ReturnNums)/SUM(Nums)*100) AS QualifiedRate,
            //IF(SUM(Nums)>SUM(TaskQuantity),100,SUM(Nums)/SUM(TaskQuantity)*100) AS FinishedRate 
            //FROM
            //(SELECT ProcecureSubName,TaskQuantity,Nums,
            //IF(ReturnNums IS NULL OR ReturnNums=0,Nums,ReturnNums) AS ReturnNums,
            //IF(Completedquantity IS NULL OR Completedquantity=0,Nums,Completedquantity) AS Completedquantity
            //FROM ("+ strView+@") a
            //JOIN Base_Department dt ON a.DeptId=dt.Id AND dt.`Name` LIKE '%"+WorkShopName+@"%'
            //WHERE TO_DAYS(CURDATE())-TO_DAYS(ReportTime)="+Day.ToString()+@"
            //) t
            //GROUP BY ProcecureSubName) finish ON plan.`Name`=finish.`Name`";
            string strSQL = @"SELECT CASE WHEN plan.`Name` LIKE '%,%' THEN  concat(substring_index ( plan.`Name`, ',', 1 ),'...') 	  ELSE  plan.`Name`  END Name ,IFNULL(plan.TaskQuantity,0) AS TaskQuantity,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.QualityNums,0) AS QualityNums,IFNULL(finish.QualifiedRate,0) AS QualifiedRate,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT ps.Id,ps.`Name` AS `Name`,IFNULL(SUM(pd.Nums),0) AS TaskQuantity
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)IN(" + Day.ToString()+","+(Day+1).ToString()+","+(Day+2).ToString()+@")
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%"+WorkShopName+ @"%'
JOIN PB_ProcecureSub ps ON ps.Id=pd.SubId
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId=device.ProcecureSubId
GROUP BY ps.Id
) plan
LEFT JOIN
(SELECT ps.Id,ps.`Name` AS `Name`,IFNULL(SUM(jb.Nums),0) AS Nums,IFNULL(SUM(jb.ReturnNums),0) AS QualityNums,
SUM(jb.StatisticsNums) AS FinishedQuantity,
IF(SUM(jb.ReturnNums)>SUM(jb.Nums),100,IF(SUM(jb.Nums) IS NULL,0,SUM(jb.ReturnNums)/SUM(jb.Nums))*100) AS QualifiedRate
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)IN(" + Day.ToString() + "," + (Day + 1).ToString() + "," + (Day + 2).ToString() + @")
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName+ @"%'
JOIN PB_ProcecureSub ps ON ps.Id=pd.SubId
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId=device.ProcecureSubId
LEFT JOIN PB_JobBooking jb ON pd.OrderDetailId=jb.ParentId AND  dp.StaffId LIKE CONCAT('%',jb.ReportUserId,'%')  AND jb.ReportTime>=dp.DispatchDate AND jb.WorkshopId=dp.DeptId
GROUP BY ps.Id
) finish
ON plan.Id=finish.Id
UNION
SELECT IFNULL(plan.`Name`,'未知') AS `Name`,IFNULL(plan.TaskQuantity,0) AS TaskQuantity,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.QualityNums,0) AS QualityNums,IFNULL(finish.QualifiedRate,0) AS QualifiedRate,IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT ps.Id,IFNULL(ps.`Name`,'未知') AS `Name`,IFNULL(SUM(pd.Nums),0) AS TaskQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)IN(" + Day.ToString() + "," + (Day + 1).ToString() + "," + (Day + 2).ToString() + @")
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName+ @"%'
LEFT JOIN Base_User u ON u.Id=dp.StaffId
GROUP BY ps.Id) plan
LEFT JOIN
(SELECT ps.Id,IFNULL(ps.`Name`,'未知') AS `Name`,IFNULL(SUM(cb.WorkReportQuantity),0) AS Nums,
IFNULL(SUM(cb.QualityNums),0) AS QualityNums, 
IFNULL(SUM(cb.StatisticalQuantity),0) AS FinishedQuantity,
IF(SUM(cb.QualityNums)>SUM(cb.WorkReportQuantity),100,IF(SUM(cb.WorkReportQuantity) IS NULL,0,SUM(cb.QualityNums)/SUM(cb.WorkReportQuantity))*100) AS QualifiedRate
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)IN(" + Day.ToString() + "," + (Day + 1).ToString() + "," + (Day + 2).ToString() + @")
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName+@"%'
LEFT JOIN Base_User u ON u.Id=dp.StaffId
LEFT JOIN PB_CirculationBill cb ON pd.OrderDetailId=cb.TaskDetailId AND cb.DispatchId=dp.Id AND cb.MaterialId=pd.MaterialId AND cb.DeptId=dp.DeptId AND cb.ProcedureId=pd.SubId AND cb.TaskQuantity=pd.Nums AND cb.SendTime>=dp.DispatchDate AND cb.DeptId=dp.DeptId
LEFT JOIN PB_ShiftType st ON dp.TeamType=st.Id AND SUBSTRING(cb.Reamrk,LOCATE('TeamTypeName',cb.Reamrk)+15,1)=st.`Name`
GROUP BY ps.Id) finish
ON plan.Id=finish.Id";
            var res = await Db.GetListBySqlAsync<PB_DispatchDetailPassRateDTO>(strSQL);
            if (res.Count > 0)
            {
                res.ForEach(aData =>
                {
                    //四舍五入保留两位小数
                    aData.TaskQuantity = Convert.ToDouble(aData.TaskQuantity.ToString("#0.##"));
                    aData.Nums = Convert.ToDouble(aData.Nums.ToString("#0.##"));
                    aData.FinishedQuantity = Convert.ToDouble(aData.FinishedQuantity.ToString("#0.##"));
                    aData.QualifiedRate = (Convert.ToDouble(aData.QualifiedRate).ToString("#0.##")).ToString() + "%";
                    aData.FinishedRate = (Convert.ToDouble(aData.FinishedRate).ToString("#0.##")).ToString() + "%";
                });
            }
            return res;
        }
        /// <summary>
        /// 工单看板
        /// </summary>
        /// <param name="WorkShopName">车间名称</param>
        /// <param name="Day">前第几天的当日</param>
        /// <returns>当前三日各派工完成率</returns>
        public async Task<List<PB_CirculationBillDispatchPadDTO>> GetTodayPassRateAsync(string WorkShopName, int Day)
        {          
            //按照工序和物料汇总的完成情况
            string strSQL = @"(SELECT plan.WorkerImg,CASE WHEN plan.`Name` LIKE '%,%' THEN  concat(substring_index (plan.`Name`, ',', 1 ),'...') 	  ELSE  plan.`Name` 
	END Name ,plan.WorkerDate,plan.POName,plan.ProceName,plan.MaterName,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT dp.Id,dp.StaffId,u.HeadImg AS WorkerImg,dp.StaffName AS `Name`,dp.OrderDetailId,dp.DispatchDate AS WorkerDate,po.POName,dp.ProceName,dp.MaterName,dp.Nums AS TaskQuantity
FROM
(SELECT dp.Id,dp.StaffId,dp.StaffName,dp.DeptId,pd.OrderDetailId,dp.DispatchDate,pd.SubId,ps.`Name` AS ProceName,m.`Name` AS MaterName,pd.MaterialId,SUM(pd.Nums) AS Nums
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN PB_ProcecureSub ps ON ps.Id=pd.SubId
JOIN PB_Material m ON pd.MaterialId=m.Id
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
GROUP BY dp.Id,pd.OrderDetailId,pd.SubId,pd.MaterialId) dp
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON dp.SubId=device.ProcecureSubId
LEFT JOIN Base_User u ON SUBSTRING_INDEX(dp.StaffId, ',', 1)=u.Id
LEFT JOIN PB_ProductionOrderDetail od ON dp.OrderDetailId=od.Id
LEFT JOIN PB_ProductionOrder po ON od.ParentId=po.Id
) plan
LEFT JOIN
(SELECT dp.Id,dp.StaffId,dp.OrderDetailId,dp.DeptId,dp.DispatchDate,IFNULL(SUM(jb.Nums),0) AS Nums,
SUM(jb.StatisticsNums) AS FinishedQuantity
FROM
(SELECT dp.Id,dp.StaffId,dp.DeptId,pd.OrderDetailId,dp.DispatchDate,pd.SubId,SUM(pd.Nums) AS Nums
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
GROUP BY dp.Id,pd.OrderDetailId,pd.SubId,pd.MaterialId) dp
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON dp.SubId=device.ProcecureSubId
LEFT JOIN PB_JobBooking jb ON dp.OrderDetailId=jb.ParentId AND  dp.StaffId LIKE CONCAT('%',jb.ReportUserId,'%')  AND jb.ReportTime>=dp.DispatchDate AND jb.WorkshopId=dp.DeptId
GROUP BY dp.Id,dp.OrderDetailId,dp.Nums
) finish
ON plan.Id=finish.Id AND plan.OrderDetailId=finish.OrderDetailId AND plan.TaskQuantity=finish.Nums
ORDER BY plan.`Name`)
UNION 
(SELECT plan.WorkerImg,plan.`Name`,plan.WorkerDate,plan.POName,plan.ProceName,plan.MaterName,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT pd.Id,u.HeadImg AS WorkerImg,dp.StaffName AS `Name`,dp.DispatchDate AS WorkerDate,po.POName,IFNULL(ps.`Name`,'未知') AS ProceName,m.`Name` AS MaterName,pd.Nums AS TaskQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Material m ON pd.MaterialId=m.Id
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
JOIN PB_ProductionOrderDetail od ON pd.OrderDetailId=od.Id
JOIN PB_ProductionOrder po ON od.ParentId=po.Id
LEFT JOIN Base_User u ON u.Id=dp.StaffId
) plan
LEFT JOIN
(SELECT pd.Id,IFNULL(SUM(cb.WorkReportQuantity),0) AS Nums,
SUM(cb.StatisticalQuantity) AS FinishedQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
JOIN PB_ProductionOrderDetail od ON pd.OrderDetailId=od.Id
JOIN PB_ProductionOrder po ON od.ParentId=po.Id
LEFT JOIN Base_User u ON u.Id=dp.StaffId
LEFT JOIN PB_CirculationBill cb ON pd.OrderDetailId=cb.TaskDetailId AND cb.DispatchId=dp.Id AND cb.MaterialId=pd.MaterialId AND cb.DeptId=dp.DeptId AND cb.ProcedureId=pd.SubId AND cb.TaskQuantity=pd.Nums
LEFT JOIN PB_ShiftType st ON dp.TeamType=st.Id AND SUBSTRING(cb.Reamrk,LOCATE('TeamTypeName',cb.Reamrk)+15,1)=st.`Name`
GROUP BY pd.Id
) finish ON plan.Id=finish.Id
ORDER BY plan.`Name`)
LIMIT 12";
            var res = await Db.GetListBySqlAsync<PB_CirculationBillDispatchPadDTO>(strSQL);
            Day++;
strSQL = @"(SELECT plan.WorkerImg,CASE WHEN plan.`Name` LIKE '%,%' THEN  concat(substring_index (plan.`Name`, ',', 1 ),'...') 	  ELSE  plan.`Name` 
	END Name ,plan.WorkerDate,plan.POName,plan.ProceName,plan.MaterName,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT dp.Id,dp.StaffId,u.HeadImg AS WorkerImg,dp.StaffName AS `Name`,dp.OrderDetailId,dp.DispatchDate AS WorkerDate,po.POName,dp.ProceName,dp.MaterName,dp.Nums AS TaskQuantity
FROM
(SELECT dp.Id,dp.StaffId,dp.StaffName,dp.DeptId,pd.OrderDetailId,dp.DispatchDate,pd.SubId,ps.`Name` AS ProceName,m.`Name` AS MaterName,pd.MaterialId,SUM(pd.Nums) AS Nums
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN PB_ProcecureSub ps ON ps.Id=pd.SubId
JOIN PB_Material m ON pd.MaterialId=m.Id
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
GROUP BY dp.Id,pd.OrderDetailId,pd.SubId,pd.MaterialId) dp
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON dp.SubId=device.ProcecureSubId
LEFT JOIN Base_User u ON SUBSTRING_INDEX(dp.StaffId, ',', 1)=u.Id
LEFT JOIN PB_ProductionOrderDetail od ON dp.OrderDetailId=od.Id
LEFT JOIN PB_ProductionOrder po ON od.ParentId=po.Id
) plan
LEFT JOIN
(SELECT dp.Id,dp.StaffId,dp.OrderDetailId,dp.DeptId,dp.DispatchDate,IFNULL(SUM(jb.Nums),0) AS Nums,
SUM(jb.StatisticsNums) AS FinishedQuantity
FROM
(SELECT dp.Id,dp.StaffId,dp.DeptId,pd.OrderDetailId,dp.DispatchDate,pd.SubId,SUM(pd.Nums) AS Nums
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
GROUP BY dp.Id,pd.OrderDetailId,pd.SubId,pd.MaterialId) dp
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON dp.SubId=device.ProcecureSubId
LEFT JOIN PB_JobBooking jb ON dp.OrderDetailId=jb.ParentId AND  dp.StaffId LIKE CONCAT('%',jb.ReportUserId,'%')  AND jb.ReportTime>=dp.DispatchDate AND jb.WorkshopId=dp.DeptId
GROUP BY dp.Id,dp.OrderDetailId,dp.Nums
) finish
ON plan.Id=finish.Id AND plan.OrderDetailId=finish.OrderDetailId AND plan.TaskQuantity=finish.Nums
ORDER BY plan.`Name`)
UNION 
(SELECT plan.WorkerImg,plan.`Name`,plan.WorkerDate,plan.POName,plan.ProceName,plan.MaterName,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT pd.Id,u.HeadImg AS WorkerImg,dp.StaffName AS `Name`,dp.DispatchDate AS WorkerDate,po.POName,IFNULL(ps.`Name`,'未知') AS ProceName,m.`Name` AS MaterName,pd.Nums AS TaskQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Material m ON pd.MaterialId=m.Id
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
JOIN PB_ProductionOrderDetail od ON pd.OrderDetailId=od.Id
JOIN PB_ProductionOrder po ON od.ParentId=po.Id
LEFT JOIN Base_User u ON u.Id=dp.StaffId
) plan
LEFT JOIN
(SELECT pd.Id,IFNULL(SUM(cb.WorkReportQuantity),0) AS Nums,
SUM(cb.StatisticalQuantity) AS FinishedQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
JOIN PB_ProductionOrderDetail od ON pd.OrderDetailId=od.Id
JOIN PB_ProductionOrder po ON od.ParentId=po.Id
LEFT JOIN Base_User u ON u.Id=dp.StaffId
LEFT JOIN PB_CirculationBill cb ON pd.OrderDetailId=cb.TaskDetailId AND cb.DispatchId=dp.Id AND cb.MaterialId=pd.MaterialId AND cb.DeptId=dp.DeptId AND cb.ProcedureId=pd.SubId AND cb.TaskQuantity=pd.Nums
LEFT JOIN PB_ShiftType st ON dp.TeamType=st.Id AND SUBSTRING(cb.Reamrk,LOCATE('TeamTypeName',cb.Reamrk)+15,1)=st.`Name`
GROUP BY pd.Id
) finish ON plan.Id=finish.Id
ORDER BY plan.`Name`)
LIMIT 12";
            List<PB_CirculationBillDispatchPadDTO> temp = await Db.GetListBySqlAsync<PB_CirculationBillDispatchPadDTO>(strSQL);
            res.AddRange(temp);
            Day++;
strSQL = @"(SELECT plan.WorkerImg,CASE WHEN plan.`Name` LIKE '%,%' THEN  concat(substring_index (plan.`Name`, ',', 1 ),'...')  ELSE  plan.`Name` END Name ,plan.WorkerDate,plan.POName,plan.ProceName,plan.MaterName,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT dp.Id,dp.StaffId,u.HeadImg AS WorkerImg,dp.StaffName AS `Name`,dp.OrderDetailId,dp.DispatchDate AS WorkerDate,po.POName,dp.ProceName,dp.MaterName,dp.Nums AS TaskQuantity
FROM
(SELECT dp.Id,dp.StaffId,dp.StaffName,dp.DeptId,pd.OrderDetailId,dp.DispatchDate,pd.SubId,ps.`Name` AS ProceName,m.`Name` AS MaterName,pd.MaterialId,SUM(pd.Nums) AS Nums
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN PB_ProcecureSub ps ON ps.Id=pd.SubId
JOIN PB_Material m ON pd.MaterialId=m.Id
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
GROUP BY dp.Id,pd.OrderDetailId,pd.SubId,pd.MaterialId) dp
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON dp.SubId=device.ProcecureSubId
LEFT JOIN Base_User u ON SUBSTRING_INDEX(dp.StaffId, ',', 1)=u.Id
LEFT JOIN PB_ProductionOrderDetail od ON dp.OrderDetailId=od.Id
LEFT JOIN PB_ProductionOrder po ON od.ParentId=po.Id
) plan
LEFT JOIN
(SELECT dp.Id,dp.StaffId,dp.OrderDetailId,dp.DeptId,dp.DispatchDate,IFNULL(SUM(jb.Nums),0) AS Nums,
SUM(jb.StatisticsNums) AS FinishedQuantity
FROM
(SELECT dp.Id,dp.StaffId,dp.DeptId,pd.OrderDetailId,dp.DispatchDate,pd.SubId,SUM(pd.Nums) AS Nums
FROM PB_DispatchDetail pd 
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
GROUP BY dp.Id,pd.OrderDetailId,pd.SubId,pd.MaterialId) dp
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON dp.SubId=device.ProcecureSubId
LEFT JOIN PB_JobBooking jb ON dp.OrderDetailId=jb.ParentId AND  dp.StaffId LIKE CONCAT('%',jb.ReportUserId,'%')  AND jb.ReportTime>=dp.DispatchDate AND jb.WorkshopId=dp.DeptId
GROUP BY dp.Id,dp.OrderDetailId,dp.Nums
) finish
ON plan.Id=finish.Id AND plan.OrderDetailId=finish.OrderDetailId AND plan.TaskQuantity=finish.Nums
ORDER BY plan.`Name`)
UNION 
(SELECT plan.WorkerImg,plan.`Name`,plan.WorkerDate,plan.POName,plan.ProceName,plan.MaterName,plan.TaskQuantity,IFNULL(finish.Nums,0) AS Nums,IFNULL(finish.FinishedQuantity,0) AS FinishedQuantity,
IF(IFNULL(finish.FinishedQuantity,0)>plan.TaskQuantity,100,IFNULL(finish.FinishedQuantity,0)/plan.TaskQuantity*100) AS FinishedRate
FROM
(SELECT pd.Id,u.HeadImg AS WorkerImg,dp.StaffName AS `Name`,dp.DispatchDate AS WorkerDate,po.POName,IFNULL(ps.`Name`,'未知') AS ProceName,m.`Name` AS MaterName,pd.Nums AS TaskQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Material m ON pd.MaterialId=m.Id
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
JOIN PB_ProductionOrderDetail od ON pd.OrderDetailId=od.Id
JOIN PB_ProductionOrder po ON od.ParentId=po.Id
LEFT JOIN Base_User u ON u.Id=dp.StaffId
) plan
LEFT JOIN
(SELECT pd.Id,IFNULL(SUM(cb.WorkReportQuantity),0) AS Nums,
SUM(cb.StatisticalQuantity) AS FinishedQuantity
FROM PB_DispatchDetail pd
LEFT JOIN PB_ProcecureSub ps ON pd.SubId=ps.Id
JOIN (SELECT DISTINCT d.ProcecureSubId FROM PB_JobBooking jb JOIN Base_Device d ON jb.DeviceId=d.Id) device ON pd.SubId<>device.ProcecureSubId
JOIN PB_Dispatch dp ON pd.ParentId=dp.Id AND TO_DAYS(CURDATE())-TO_DAYS(dp.DispatchDate)=" + Day.ToString() + @"
JOIN Base_Department d ON dp.DeptId=d.Id AND d.FullName LIKE '%" + WorkShopName + @"%'
JOIN PB_ProductionOrderDetail od ON pd.OrderDetailId=od.Id
JOIN PB_ProductionOrder po ON od.ParentId=po.Id
LEFT JOIN Base_User u ON u.Id=dp.StaffId
LEFT JOIN PB_CirculationBill cb ON pd.OrderDetailId=cb.TaskDetailId AND cb.DispatchId=dp.Id AND cb.MaterialId=pd.MaterialId AND cb.DeptId=dp.DeptId AND cb.ProcedureId=pd.SubId AND cb.TaskQuantity=pd.Nums
LEFT JOIN PB_ShiftType st ON dp.TeamType=st.Id AND SUBSTRING(cb.Reamrk,LOCATE('TeamTypeName',cb.Reamrk)+15,1)=st.`Name`
GROUP BY pd.Id
) finish ON plan.Id=finish.Id
ORDER BY plan.`Name`)
LIMIT 12";
            temp = await Db.GetListBySqlAsync<PB_CirculationBillDispatchPadDTO>(strSQL);
            res.AddRange(temp);
            if (res.Count > 0)
            {
                res.ForEach(aData =>
                {
                    aData.WorkerDate = Convert.ToDateTime(aData.WorkerDate).ToString("MM月dd日");
                    //四舍五入保留两位小数
                    aData.TaskQuantity = Convert.ToDouble(aData.TaskQuantity.ToString("#0.##"));
                    aData.Nums = Convert.ToDouble(aData.Nums.ToString("#0.##"));
                    aData.FinishedQuantity = Convert.ToDouble(aData.FinishedQuantity.ToString("#0.##"));
                    aData.FinishedRate = (Convert.ToDouble(aData.FinishedRate).ToString("#0.##")).ToString() + "%";
                });
            }
            return res;
        }
        /// <summary>
        /// 生产维度查询报表
        /// </summary>
        /// <param name="input">查询条件</param>
        /// <returns></returns>
        public async Task<PageResult<PB_CirculationBillDataReportDTO>> GetCirculationBillDataReport(PageInput<PB_CirculationBillDataReportConditionDTO> input)
        {
            string strSQL = @"SELECT
	* 
FROM
	(
	SELECT
	Bill.Id CirId,
		a.Id DispatchId,
		a.DispatchDate,
		c.POName POName,
		sub.`Name` ProceName,
		a.StaffName StaffName,
		Mater.`Code`,
		Mater.`Name` MaterName,
		Mater.`Spec`,
		b.Nums TaskQuantity,
		IFNULL( Bill.StatisticalQuantity, 0 ) AS FinishedQuantity,
		Mater.`Name` SubordinateProduct,
		Department.`Name` WorkShopName ,
		sub.Id  ProceId,
		Department.Id DeptId
	
	FROM
		PB_Dispatch AS a
		INNER JOIN PB_DispatchDetail AS b ON a.Id = b.ParentId
		INNER JOIN PB_ProductionOrder AS c ON b.OrderId = c.Id
		INNER JOIN PB_ProcecureSub AS sub ON b.SubId = sub.Id
		INNER JOIN PB_Material AS Mater ON b.MaterialId = Mater.Id
		INNER JOIN PB_CirculationBill AS Bill ON a.Id = Bill.DispatchId  AND Bill.MaterialId=b.MaterialId
		AND Bill.DispatchDetailId = b.Id
		INNER JOIN Base_Department AS Department ON a.DeptId = Department.Id 
	WHERE
		a.State = 0 
 UNION ALL
	SELECT
	jobing.Id CirId,
		a.Id DispatchId,
		a.DispatchDate,
		c.POName POName,
		sub.`Name` ProceName,
		a.StaffName StaffName,
		Mater.`Code`,
		Mater.`Name` MaterName,
		Mater.`Spec`,
		b.Nums TaskQuantity,
		IFNULL( jobing.StatisticsNums, 0 ) AS FinishedQuantity,
		Mater.`Name` SubordinateProduct,
		Department.`Name` WorkShopName ,
	  sub.Id  ProceId,
		Department.Id DeptId
	FROM
		PB_Dispatch AS a
		INNER JOIN PB_DispatchDetail AS b ON a.Id = b.ParentId
		INNER JOIN PB_ProductionOrder AS c ON b.OrderId = c.Id
		INNER  JOIN PB_Material AS Mater ON b.MaterialId = Mater.Id
		INNER JOIN PB_CirculationBill AS Bill ON a.Id = Bill.DispatchId  AND Bill.MaterialId=b.MaterialId
		INNER JOIN PB_JobBooking AS jobing ON jobing.CirBillId = Bill.Id
		INNER JOIN Base_Department AS Department ON a.DeptId = Department.Id
	 INNER JOIN Base_Device AS Device on  jobing.DeviceId=Device.Id
	 INNER  JOIN PB_ProcecureSub AS sub ON Device.ProcecureSubId = sub.Id
	WHERE
		a.State = 0  
	
	) AS T where 1=1 ";
            string _where = string.Empty;
            if (!input.Search.Key.IsNullOrEmpty())
            {
                _where += " AND (T.`Code` LIKE '%"+input.Search.Key.Trim()+@"%' OR T.MaterName LIKE '%"+input.Search.Key.Trim()+@"%')";
            }
            if (!input.Search.ProceId.IsNullOrEmpty())
            {
                _where += @" AND T.ProceId='"+input.Search.ProceId +@"'";
            }
            if (!input.Search.DeptId.IsNullOrEmpty())
            {
                _where += " AND T.DeptId='"+input.Search.DeptId+@"'";
            }
            if (!input.Search.POName.IsNullOrEmpty())
            {
                _where += " AND T.POName LIKE '%"+ input.Search.POName.Trim() +@"%'";
            }
            if (!input.Search.StartDate.IsNullOrEmpty())
            {
                _where += " and T.DispatchDate>='" + Convert.ToDateTime(input.Search.StartDate).ToString("yyyy-MM-dd 00:00:00") + "'";
            }
            if (!input.Search.EndDate.IsNullOrEmpty())
            {
                _where += " and T.DispatchDate<='" + Convert.ToDateTime(input.Search.EndDate).ToString("yyyy-MM-dd 23:59:59") + "'";
            }
            if (!input.Search.DispatchId.IsNullOrEmpty())
            {
                _where += " AND T.DispatchId LIKE '%" + input.Search.DispatchId+"%'";
            }
            if (!input.SortField.IsNullOrEmpty())
            {
                _where += " ORDER BY " + input.SortField;
            }
            if (!input.SortType.IsNullOrEmpty())
            {
                _where += " " + input.SortType;
            }
            strSQL += _where;
            var res = await Db.GetListBySqlAsync<PB_CirculationBillDataReportDTO>(strSQL);
            int pagecount;//总页数
            List<PB_CirculationBillDataReportDTO> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<PB_CirculationBillDataReportDTO> productes = new List<PB_CirculationBillDataReportDTO>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<PB_CirculationBillDataReportDTO> { Data = productes, Total = pagees };
        }

        /// <summary>
        /// 计划维度查询报表
        /// </summary>
        /// <param name="input">查询条件</param>
        /// <returns></returns>
        public async Task<PageResult<PB_CirculationBillDataReportDTO>> GetOrderDataReport(PageInput<PB_CirculationBillDataReportConditionDTO> input)
        {
            string strSQL = @"SELECT t.Id,t.StartDate,t.EndDate,t.DeptId,t.WorkShopName,t.POName,t.`Code`,t.MaterName,t.Spec,t.TaskQuantity,t.FinishedQuantity,t.CreateTime 
FROM
(SELECT pd.Id,pd.StartDate,pd.EndDate,po.DeptId,d.FullName AS WorkShopName,po.POName,m.`Code`,m.`Name` AS MaterName,m.Spec,pd.Productionquantity AS TaskQuantity,
SUM(IFNULL(pd.CompleteNums,0)) AS FinishedQuantity,pd.CreateTime
FROM PB_ProductionOrderDetail pd 
JOIN PB_ProductionOrder po ON pd.ParentId=po.Id
LEFT JOIN Base_Department d ON po.DeptId=d.Id
LEFT JOIN PB_Material m ON pd.MaterialId=m.Id
GROUP BY pd.Id) t
WHERE 1=1";
            string _where = string.Empty;
            if (!input.Search.Key.IsNullOrEmpty())
            {
                _where += " AND (t.`Code` LIKE '%" + input.Search.Key.Trim() + @"%' OR t.MaterName LIKE '%" + input.Search.Key.Trim() + @"%')";
            }
            if (!input.Search.DeptId.IsNullOrEmpty())
            {
                _where += " AND t.DeptId='" + input.Search.DeptId + @"'";
            }
            if (!input.Search.POName.IsNullOrEmpty())
            {
                _where += " AND POName LIKE '%"+ input.Search.POName +@"%'";
            }
            if (!input.Search.StartDate.IsNullOrEmpty())
            {
                _where += " and t.CreateTime>='" + Convert.ToDateTime(input.Search.StartDate).ToString("yyyy-MM-dd 00:00:00") + "'";
            }
            if (!input.Search.EndDate.IsNullOrEmpty())
            {
                _where += " and t.CreateTime<='" + Convert.ToDateTime(input.Search.EndDate).ToString("yyyy-MM-dd 23:59:59") + "'";
            }
            if (!input.SortField.IsNullOrEmpty())
            {
                _where += " ORDER BY " + input.SortField;
            }
            if (!input.SortType.IsNullOrEmpty())
            {
                _where += " " + input.SortType;
            }
            strSQL += _where;
            var res = await Db.GetListBySqlAsync<PB_CirculationBillDataReportDTO>(strSQL);
            int pagecount;//总页数
            List<PB_CirculationBillDataReportDTO> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<PB_CirculationBillDataReportDTO> productes = new List<PB_CirculationBillDataReportDTO>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<PB_CirculationBillDataReportDTO> { Data = productes, Total = pagees };
        }
        /// <summary>
        /// 获取钉钉用户报工工资数据列表
        /// </summary>
        /// <param name="Seach">条件</param>
        /// <returns></returns>
        public async Task<List<EmployeeSalaryQuery>> GetDingUserWages(EmployeeSalaryQuerySeach Seach)
        {
            StringBuilder sql = new StringBuilder();
            //sql.AppendLine("SELECT ");

            //sql.AppendLine("b.RealName RealName, ");
            //sql.AppendLine("b.UserCode UserCode, ");
            //sql.AppendLine("b.HeadImg HeadImg, ");
            //sql.AppendLine("m.Id MaterialId, ");
            //sql.AppendLine("m.NAME, ");
            //sql.AppendLine("u.Id ProcecureSubId, ");
            //sql.AppendLine("IFNULL(SUM(a.ReceivingQuantity), 0) AS ReceivingQuantity, ");
            //sql.AppendLine("IFNULL(SUM(a.ReceivingQuantity * price.Price), 0) AS Subtotal, ");
            //sql.AppendLine("h.Id AS DispatchId, ");
            //sql.AppendLine("IFNULL(SUM(detail.Nums), 0) AS ProductionQuantity, ");
            //sql.AppendLine("Department.`Name` AS DepartmentName ,");
            //sql.AppendLine("ShiftType.`Name` AS  ShiftTypeName,");
            //sql.AppendLine("price.Price AS price");
            //sql.AppendLine("FROM ");
            //sql.AppendLine("PB_CirculationBill AS a ");
            //sql.AppendLine("LEFT JOIN Base_User AS b ON a.WorkerId = b.Id ");
            //sql.AppendLine("LEFT JOIN PB_Material AS m ON a.MaterialId = m.Id ");
            //sql.AppendLine("LEFT JOIN PB_ProductionOrder AS t ON a.DeviceId = t.Id ");
            //sql.AppendLine("LEFT JOIN Base_User as user on a.WorkerId = user.Id ");
            //sql.AppendLine("LEFT JOIN PB_Dispatch AS h ON a.DispatchId = h.Id ");
            //sql.AppendLine("LEFT JOIN PB_ProcecureSub AS u ON a.ProcedureId = u.Id ");
            //sql.AppendLine("LEFT JOIN PB_ProcecureSubPrice AS price ON u.Id = price.SubId ");
            //sql.AppendLine("LEFT JOIN Base_Department as Department  ON user.DepartmentId = Department.Id ");
            //sql.AppendLine("LEFT JOIN PB_ShiftType AS  ShiftType  on h.TeamType=ShiftType.Id");
            //sql.AppendLine("LEFT JOIN PB_DispatchDetail AS detail on detail.ParentId = h.Id AND a.MaterialId = detail.MaterialId ");
            //sql.AppendLine("WHERE ");
            //if (!Seach.Time.IsNullOrEmpty())
            //{
            //    sql.AppendLine($" TO_DAYS(a.ReceivingTime) = TO_DAYS('{Seach.Time}') ");
            //}
            //else {
            //    sql.AppendLine(" TO_DAYS(a.ReceivingTime) = TO_DAYS(NOW()) ");
            //} 
            //sql.AppendLine($"AND user.DingUserId='{Seach.DingUserId}'");
            ////只截取指定时间段的工序 基础设定工序价格时间要和接收时间匹配上不然没数据
            //sql.AppendLine(" AND TO_DAYS( price.PriceDate  )  =TO_DAYS(   a.ReceivingTime   )");
            //sql.AppendLine("GROUP BY ");
            //sql.AppendLine(" a.ProcedureId,b.RealName,b.UserCode,b.HeadImg,m.`Name`,m.Id ,h.Id,Department.`Name`,price.Price ");
            sql.AppendLine("SELECT");
            sql.AppendLine("b.UserCode 'UserCode',");
            sql.AppendLine("b.RealName 'RealName',");
            sql.AppendLine("ifnull(b.HeadImg, '') 'HeadImg',");
            sql.AppendLine("c.Name 'DepartmentName',");
            sql.AppendLine("d.Name 'NAME',");
            sql.AppendLine("SUM(ifnull(a.WorkReportQuantity, 0)) 'ProductionQuantity',");
            sql.AppendLine("SUM(ifnull(a.StatisticalQuantity, 0)) 'ReceivingQuantity' ,");
            sql.AppendLine("ifnull(e.Price, 0) 'price',");
            sql.AppendLine("ifnull(e.Price, 0) 'thisPrice'");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_CirculationBill a");
            sql.AppendLine("LEFT JOIN Base_User b ON a.WorkerId = b.Id");
            sql.AppendLine("LEFT JOIN Base_Department c ON a.DeptId = c.Id");
            sql.AppendLine("LEFT JOIN PB_Material d ON a.MaterialId = d.Id");
            sql.AppendLine("LEFT JOIN PB_ProcecureSubPrice e ON a.ProcedureId = e.SubId and TO_DAYS(a.SendTime)= TO_DAYS(e.PriceDate)  AND d.Length>e.Upperlimit AND d.Length<e.Lowerlimit");
            sql.AppendLine("WHERE");
            sql.AppendLine($"a.WorkerId = (select Id from Base_User WHERE DingUserId = '{Seach.DingUserId}')  ");
            if (!Seach.Time.IsNullOrEmpty())
            {
                sql.AppendLine($"AND date_format(a.SendTime, '%Y-%m-%d')='{Seach.Time}' ");
            }
            else
            {
                sql.AppendLine("AND date_format(a.SendTime, '%Y-%m-%d')= date_format(now(), '%Y-%m-%d')");
            }
            sql.AppendLine("GROUP BY");
            sql.AppendLine("b.UserCode,");
            sql.AppendLine("b.RealName,");
            sql.AppendLine("b.HeadImg,");
            sql.AppendLine("c.Name,");
            sql.AppendLine("d.Name,");
            sql.AppendLine("e.Price");
            sql.AppendLine("HAVING SUM(a.StatisticalQuantity) > 0");
            sql.AppendLine("UNION  all");


            sql.AppendLine("SELECT");
            sql.AppendLine(" User.UserCode'UserCode',");
            sql.AppendLine("User.RealName,");
            sql.AppendLine("User.HeadImg 'HeadImg',");
            sql.AppendLine("dep.Name 'DepartmentName',");
            sql.AppendLine("m.Name 'NAME',");
            sql.AppendLine("SUM(ifnull(a.Nums, 0)) 'ProductionQuantity',");
            sql.AppendLine("SUM(ifnull(a.StatisticsNums, 0)) 'ReceivingQuantity' ,");
            sql.AppendLine("e.Price 'thisPrice',");
            sql.AppendLine("ifnull(e.Price  / (");
            sql.AppendLine("select Count(A.StaffId) from(");
            sql.AppendLine("SELECT");
            sql.AppendLine("Dispatch.StaffId");
            sql.AppendLine("FROM");
            sql.AppendLine(" PB_JobBooking AS a");
            sql.AppendLine("INNER JOIN Base_Department dep ON a.DeptId = dep.Id");
            sql.AppendLine("INNER JOIN Base_Device AS b ON a.DeviceId = b.Id");
            sql.AppendLine("INNER JOIN PB_ProcecureSub AS SUB ON b.ProcecureSubId = SUB.Id");
            sql.AppendLine("INNER JOIN PB_DispatchDetail AS DEta ON b.ProcecureSubId = DEta.SubId");
            sql.AppendLine("AND TO_DAYS(DEta.CreateTime) = TO_DAYS(a.CreateTime)");
            sql.AppendLine("INNER JOIN PB_Dispatch AS Dispatch ON DEta.ParentId = Dispatch.Id");
            sql.AppendLine("AND a.DeptId = Dispatch.DeptId");
            sql.AppendLine("INNER JOIN Base_User AS USER ON Dispatch.StaffId = USER.Id");
            sql.AppendLine("INNER JOIN PB_ProcecureSubPrice AS e ON b.ProcecureSubId = e.SubId");
            sql.AppendLine("AND TO_DAYS(a.TakeOverTime) = TO_DAYS(e.PriceDate)");
            sql.AppendLine("INNER JOIN PB_Material AS m ON a.MaterialId = m.Id");
            if (!Seach.Time.IsNullOrEmpty())
            {
                sql.AppendLine($"AND date_format(a.TakeOverTime, '%Y-%m-%d') = '{Seach.Time}'");
            }
            else
            {
                sql.AppendLine("AND date_format(a.TakeOverTime, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') ");
            }

            sql.AppendLine("GROUP BY");
            sql.AppendLine("Dispatch.StaffId");
            sql.AppendLine(") AS A) ");
            sql.AppendLine(", 0) 'price'");
            sql.AppendLine("FROM PB_JobBooking AS a");
            sql.AppendLine("LEFT JOIN Base_Department dep ON a.DeptId = dep.Id");
            sql.AppendLine("LEFT JOIN Base_Device AS b on a.DeviceId = b.Id");
            sql.AppendLine("LEFT JOIN PB_ProcecureSub AS SUB ON b.ProcecureSubId = SUB.Id");
            sql.AppendLine("LEFT JOIN PB_DispatchDetail AS DEta on b.ProcecureSubId = DEta.SubId AND TO_DAYS(DEta.CreateTime)= TO_DAYS(a.CreateTime)");
            sql.AppendLine("LEFT JOIN  PB_Dispatch AS Dispatch on DEta.ParentId = Dispatch.Id AND a.DeptId = Dispatch.DeptId");
            sql.AppendLine("LEFT JOIN  Base_User AS User on Dispatch.StaffId = User.Id");
            sql.AppendLine("LEFT JOIN PB_ProcecureSubPrice as e on b.ProcecureSubId = e.SubId and TO_DAYS(a.TakeOverTime)= TO_DAYS(e.PriceDate) ");
            sql.AppendLine("LEFT JOIN PB_Material as m on a.MaterialId = m.Id");
            sql.AppendLine($"WHERE Dispatch.StaffId = (SELECT ID FROM Base_User WHERE DingUserId = '{Seach.DingUserId}')");
            if (!Seach.Time.IsNullOrEmpty())
            {
                sql.AppendLine($" AND date_format(a.TakeOverTime, '%Y-%m-%d')='{Seach.Time}' ");
            }
            else
            {
                sql.AppendLine("AND date_format(a.TakeOverTime, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') ");
            }
            sql.AppendLine("GROUP BY");
            sql.AppendLine("dep.Name,");
            sql.AppendLine("m.Name,");
            sql.AppendLine("e.Price,");
            sql.AppendLine("User.RealName,");
            sql.AppendLine("User.UserCode,");
            sql.AppendLine("User.HeadImg");
            sql.AppendLine("HAVING SUM(a.StatisticsNums) > 0");
            var listData = await Db.GetListBySqlAsync<EmployeeSalaryQuery>(sql.ToString());
            if (listData.Count > 0)
            {
                return listData;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取月份报工数据
        /// </summary>
        /// <param name="Seach"></param>
        /// <returns></returns>
        public async Task<List<EmployeeSalaryQueryMonth>> GetDingUserMonthWages(EmployeeSalaryQueryMonthSeach Seach)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT");
            sql.AppendLine("b.UserCode 'UserCode',");
            sql.AppendLine("b.RealName 'RealName',");
            sql.AppendLine("ifnull(b.HeadImg,'') 'HeadImg',");
            sql.AppendLine("c.Name 'DepartmentName',");
            sql.AppendLine("d.Name 'NAME',");
            sql.AppendLine("SUM(ifnull(a.WorkReportQuantity, 0)) 'ProductionQuantity',");
            sql.AppendLine("SUM(ifnull(a.StatisticalQuantity, 0)) 'ReceivingQuantity',");
            sql.AppendLine("ifnull(e.Price, 0) 'price',");
            sql.AppendLine("SUM(ifnull(a.StatisticalQuantity, 0)) * ifnull(e.Price, 0) 'Subtotal',");
            sql.AppendLine("date( a.SendTime) as SendTime");
            sql.AppendLine("FROM");
            sql.AppendLine("PB_CirculationBill a ");
            sql.AppendLine("LEFT JOIN Base_User b ON a.WorkerId = b.Id");
            sql.AppendLine("LEFT JOIN Base_Department c ON a.DeptId = c.Id");
            sql.AppendLine("LEFT JOIN PB_Material d ON a.MaterialId = d.Id");
            sql.AppendLine("LEFT JOIN PB_ProcecureSubPrice e ON a.ProcedureId = e.SubId and TO_DAYS(a.SendTime)= TO_DAYS(e.PriceDate)  AND d.Length>e.Upperlimit AND d.Length<e.Lowerlimit");
            sql.AppendLine("WHERE");
            sql.AppendLine($"a.WorkerId = (select Id from Base_User WHERE DingUserId = '{Seach.DingUserId}')       ");
            sql.AppendLine("AND ");
            if (!Seach.Year.IsNullOrEmpty() && !Seach.Month.IsNullOrEmpty())
            {
                sql.AppendLine($"YEAR(a.SendTime)='{Seach.Year}' AND MONTH(a.SendTime)='{Seach.Month}'");
            }
            else
            {
                sql.AppendLine("YEAR(a.SendTime)=YEAR(NOW()) AND MONTH(a.SendTime)=MONTH(NOW())");
            }
            sql.AppendLine("GROUP BY");
            sql.AppendLine("b.UserCode,");
            sql.AppendLine("b.RealName,");
            sql.AppendLine("b.HeadImg,");
            sql.AppendLine("c.Name,");
            sql.AppendLine("d.Name,");
            sql.AppendLine("e.Price,");
            sql.AppendLine("date( a.SendTime)");
            sql.AppendLine("HAVING SUM(a.StatisticalQuantity) > 0 ");
            sql.AppendLine("union ALL");
            sql.AppendLine("SELECT");

            sql.AppendLine("  u.UserCode 'UserCode',");
            sql.AppendLine("  u.RealName 'RealName',");
            sql.AppendLine(" ifnull(u.HeadImg, '') 'HeadImg',");
            sql.AppendLine(" dep.Name 'DepartmentName',");
            sql.AppendLine(" m.Name 'NAME',");
            sql.AppendLine(" SUM(ifnull(a.Nums, 0)) 'ProductionQuantity',");
            sql.AppendLine(" SUM(ifnull(a.StatisticsNums, 0)) 'ReceivingQuantity' ,");
            sql.AppendLine(" ifnull(e.Price, 0) 'price' ,");
            sql.AppendLine("  SUM(ifnull(a.StatisticsNums, 0)) * ifnull(e.Price, 0) 'Subtotal',");
            sql.AppendLine("  date(a.TakeOverTime) AS SendTime");
            sql.AppendLine("  FROM  PB_JobBooking AS a");
            sql.AppendLine("  LEFT JOIN Base_User  AS u ON a.ReportUserId = u.Id");
            sql.AppendLine(" LEFT JOIN Base_Department dep ON a.DeptId = dep.Id");
            sql.AppendLine("  LEFT JOIN Base_Device AS b on a.DeviceId = b.Id");
            sql.AppendLine(" LEFT JOIN PB_ProcecureSubPrice as e on b.ProcecureSubId = e.SubId and TO_DAYS(a.TakeOverTime)= TO_DAYS(e.PriceDate)");
            sql.AppendLine("  LEFT JOIN PB_Material as m on a.MaterialId = m.Id");
            sql.AppendLine($"  WHERE a.ReportUserId = (select Id FROM Base_User WHERE DingUserId = '{Seach.DingUserId}')");
            sql.AppendLine("and");
            if (!Seach.Year.IsNullOrEmpty() && !Seach.Year.IsNullOrEmpty())
            {
                sql.AppendLine($"YEAR(a.TakeOverTime)='{Seach.Year}' AND MONTH(a.TakeOverTime)='{Seach.Month}'");
            }
            else
            {
                sql.AppendLine("YEAR(a.TakeOverTime)=YEAR(NOW()) AND MONTH(a.TakeOverTime)=MONTH(NOW())");
            }
            sql.AppendLine("GROUP BY");
            sql.AppendLine("u.UserCode,");
            sql.AppendLine("u.RealName,");
            sql.AppendLine("u.HeadImg,");
            sql.AppendLine("dep.Name,");
            sql.AppendLine("m.Name,");
            sql.AppendLine("e.Price,");
            sql.AppendLine("date(a.TakeOverTime)");
            sql.AppendLine("HAVING SUM(a.StatisticsNums) > 0");
            var listData = await Db.GetListBySqlAsync<EmployeeSalaryQueryMonth>(sql.ToString());
            if (listData.Count > 0)
            {
                return listData.OrderBy(m => m.SendTime).ToList();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 托盘领取详情
        /// </summary>
        /// <param name="trayId">托盘Id</param>
        /// <param name="CirBillId">流转卡Id</param>
        /// <returns></returns>
        public async Task<List<CirculationBillItemDTO>> GetCirculationBillItemAsync(string trayId,string CirBillId)
        {
            string sql = @"select a.Id,a.CollectionUserId,b.RealName as CollectionUserName,a.CollectionTime,a.Nums from PB_CirculationBillItem as a
left join Base_User as b on a.CollectionUserId=b.Id
where a.CirBillId='{1}'";
            string sqlMain = string.Format(sql, trayId, CirBillId);
            List<CirculationBillItemDTO> listdata = await Db.GetListBySqlAsync<CirculationBillItemDTO>(sqlMain);
            return listdata;
        }

        #endregion

        #region 私有成员

        #endregion

        #region  半成品领料

        /// <summary>
        /// 判断派工单是否是自己的派工单
        /// </summary>
        /// <param name="DispatchDetailQrCode">派工单明细二维码</param>
        /// <returns></returns>
        public async Task<PB_DispatchDetail> GetDispatchDetailAsync(string DispatchDetailQrCode)
        {
            //派工单明细Id
            string DispatchDetailId = DispatchDetailQrCode.Split(',')?[1].ToString();

            PB_DispatchDetail disdata = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == DispatchDetailId).FirstOrDefaultAsync();
            PB_Dispatch Dispatch = await Db.GetIQueryable<PB_Dispatch>().Where(x => x.Id == disdata.ParentId).FirstOrDefaultAsync();
            if (!Dispatch.StaffId.Contains(_operator.UserId))
            {
                throw new BusException("请扫描自己的派工单信息");
            }
            return disdata;
        }

        /// <summary>
        /// 获取流转卡对应的半成品信息
        /// </summary>
        /// <param name="CirBillQrCode">流转卡二维码</param>
        /// <returns></returns>
        public async Task<ReceiveCirBillMaterialDTO> GetReceiveCirBillMaterialDataAsync(string CirBillQrCode)
        {
            //流转单Id
            string CirBillId = CirBillQrCode.Split(',')?[1].ToString();
            //获取流转单的半成品信息
            Expression<Func<PB_CirculationBill, PB_Material, ReceiveCirBillMaterialDTO>> select = (a, b) => new ReceiveCirBillMaterialDTO
            {
                CirBillId = a.Id,
                MaterialId = a.MaterialId,
                MaterialName = b.Name,
                MaterialCode = b.Code,
                MaterialSpec = b.Spec,
                Receiver = a.Receiver,
                Nums = (a.ReceivingQuantity == null ? 0 : a.ReceivingQuantity) - (a.ReadyNums == null ? 0 : a.ReadyNums),
                QrCode = a.QrCode
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_CirculationBill>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var res = await q.Where(x => x.CirBillId == CirBillId).FirstOrDefaultAsync();
            if (res.Receiver.IsNullOrEmpty())
            {
                throw new BusException("流转卡未接收,工序接收后再领取");
            }
            return res;
        }

        /// <summary>
        /// 半成品领取保存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [Transactional]
        public async Task SaveReceiveMaterialAsync(ReceiveMaterialDTO data)
        {
            //派工单明细Id
            string DispatchDetailId = data.DispatchDetailQrCode.Split(',')?[1].ToString();

            //托盘Id
            string TrayId = data.TrayQrCode.Split(',')?[1].ToString();

            //流转单Id
            string CirBillId = data.CirBillQrCode.Split(',')?[1].ToString();

            //获取派工单明细信息
            PB_DispatchDetail DesignDetail = await Db.GetIQueryable<PB_DispatchDetail>().Where(x => x.Id == DispatchDetailId).FirstOrDefaultAsync();

            //获取流转卡信息
            PB_CirculationBill cirdata = await Db.GetIQueryable<PB_CirculationBill>().Where(x => x.Id == CirBillId && x.TrayId == TrayId).FirstOrDefaultAsync();
            if (cirdata == null)
            {
                throw new BusException("请检查流转单是否在托盘上");
            }

            //添加半成品领料信息
            PB_CirculationBillItem BillItem = new PB_CirculationBillItem();
            BillItem.Id = IdHelper.GetId();
            BillItem.CirBillId = CirBillId;
            BillItem.DispatchId = DesignDetail.ParentId;
            BillItem.DispatchDetailId = DesignDetail.Id;
            BillItem.TrayId = TrayId;
            BillItem.OrderId = DesignDetail.OrderId;
            BillItem.OrderDetailId = DesignDetail.OrderDetailId;
            BillItem.MaterialId = cirdata.MaterialId;
            BillItem.Nums = data.ReceiveNums;
            BillItem.CollectionTime = DateTime.Now;
            BillItem.CollectionUserId = _operator.UserId;
            BillItem.CreatorId = _operator.UserId;
            BillItem.CreateTime = DateTime.Now;
            BillItem.Deleted = false;
            BillItem.SubordinateProduct = cirdata.SubordinateProduct;
            await Db.InsertAsync<PB_CirculationBillItem>(BillItem);

            //修改流转卡被领取的数量
            string sqlMain = @"update PB_CirculationBill set ReadyNums=IFNULL(ReadyNums,0)+" + data.ReceiveNums + " where Id='" + CirBillId + "'";
            await ExecuteSqlAsync(sqlMain);

            double? nums = 0;
            if (data.ReceiveNums.ToString().IsNullOrEmpty())
            {
                nums = data.ReceiveNums;
            }
            else
            {
                nums = data.ReceiveNums + cirdata.ReadyNums;
            }

            //领取数量等于流转卡的统计数量,托盘上去掉流转卡信息
            if (nums == cirdata.ReceivingQuantity)
            {
                string sql = @" delete from PB_TrayCirculationBill where TrayId='" + TrayId + "' and CirBillId='" + CirBillId + "' ";
                await ExecuteSqlAsync(sql);
            }
        }

        /// <summary>
        /// 扫描流转卡黑件生成入库通知单收货单反写任务单已生产数量
        /// </summary>
        /// <param name="blackScanningDto"></param>
        /// <param name="_mongodbOptions"></param>
        /// <param name="_redisOptions"></param>
        /// <returns></returns>
        /// <summary>
        public async Task<string> SubmitBlackScanning(BlackScanningDto blackScanningDto, MongoDBOptions _mongodbOptions, RedisOptions _redisOptions)
        {
            string jobId = IdHelper.GetId();
            //定义sql
            string sql = string.Empty;
            string memo = string.Empty;
            string packguid = string.Empty;
            var base_user = await Db.GetIQueryable<Base_User>().Where(m => m.Id == _operator.UserId).FirstOrDefaultAsync();
            //入库通知单
            string NoticeId = IdHelper.GetId();
            //收货单Id
            string Receivingid = IdHelper.GetId();
            List<BlackCirculationBillDetail> blackCirculationBillDetails = new List<BlackCirculationBillDetail>();
            blackCirculationBillDetails = blackScanningDto.detail;
            var list = blackCirculationBillDetails.GroupBy(s => new { s.MaterialId, s.OrderDetailId, s.OrderId, s.ProductName }).Select(x => new {
                MaterialId = x.Key.MaterialId,
                OrderDetailId = x.Key.OrderDetailId,
                OrderId = x.Key.OrderId,
                ProductName = x.Key.ProductName,
                StatisticalQuantity = x.Sum(i => i.StatisticalQuantity)
            }).ToList();
            List<string> ids = new List<string>();
            foreach (var item in list)
            {
                ids.Add(item.MaterialId);
            }
            string OrderId = list.FirstOrDefault().OrderId;
            //时间戳
            string timestamp = GetTimeStamp();
            //推送队列批次
            string strguid = Guid.NewGuid().ToString("N");
            //实例化Redis操作类
            _redisOptions.DbChannel = (int)Redis_Type.redis6;
            RedisSetHelper redis = new RedisSetHelper(_redisOptions);
            List<PB_PackList> listpack = new List<PB_PackList>();
            List<PB_PackListInfo> listinfo = new List<PB_PackListInfo>();
            List<PrintQueue> listdata = new List<PrintQueue>();
            List<PB_JobBookingQrCode> listcode = new List<PB_JobBookingQrCode>();
            List<PB_JobBookingDetailed> pbdetailed = new List<PB_JobBookingDetailed>();
            Base_User bdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            var deptdata = await _opdept.GetOperatorDept();
            //判断是否是是自动打包，否
            if (blackScanningDto.IsPushQueue == false)
            {

                foreach (var item in list)
                {
                    var pb_mater = await Db.GetIQueryable<PB_Material>().Where(m => m.Id == item.MaterialId).FirstOrDefaultAsync();
                    if (pb_mater.IsSingle == true)
                    {
                        PrintQueue pq = new PrintQueue();
                        PB_JobBookingQrCode jkcode = new PB_JobBookingQrCode();

                        string jobcodeId = IdHelper.GetId();   //成品档案表ID
                        pq.Id = IdHelper.GetId();
                        pq.Name = pb_mater.Name;
                        pq.Date = DateTime.Now.ToString("yyyy-MM-dd");
                        pq.Dept = pb_mater.IsMine == true ? deptdata.DeptName : pb_mater.SupId;
                        if (RedisSetHelper.Exists("QualityTestingNumber"))
                        {
                            string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                            if (redis.GetString("QualityTestingTime") == thedate)
                            {
                                redis.SetString("QualityTestingNumber", (Convert.ToInt32(redis.GetString("QualityTestingNumber")) + 1).ToString());
                                pq.No = jobcodeId + "," + redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                            }
                            else
                            {
                                redis.SetString("QualityTestingTime", thedate);
                                redis.SetString("QualityTestingNumber", "1");
                                pq.No = jobcodeId + "," + redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                            }
                        }
                        else
                        {
                            redis.SetString("QualityTestingNumber", "0");
                        }
                        pq.QrCode = h_Material + "," + item.MaterialId;
                        pq.TypeName = pb_mater.Spec;
                        pq.Type = Skill_Type.Statistics.ToString();
                        pq.Status = item.StatisticalQuantity.ToString();
                        pq.Printer = blackScanningDto.MaterialPrinterId;
                        pq.IsProduct = pb_mater.IsProduct;
                        pq.BatchNumber = strguid;
                        pq.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                        pq.JobCount = 1;
                        listdata.Add(pq);
                        jkcode.Id = jobcodeId;
                        jkcode.ParentId = jobId;//黑件报工主表id
                        jkcode.QrCode = h_Material + "," + item.MaterialId;
                        jkcode.MaterialId = item.MaterialId;
                        jkcode.MaterialName = pb_mater.Name;
                        jkcode.OldOrNew = 1;
                        jkcode.RecId = Receivingid;
                        listcode.Add(jkcode);
                        PB_JobBookingDetailed pbdata = new PB_JobBookingDetailed();
                        pbdata.Id = IdHelper.GetId();
                        pbdata.No = "";
                        pbdata.Nums = 1;
                        pbdata.DeptId = deptdata.DetpId;
                        pbdata.OperationUserId = _operator.UserId;
                        pbdata.OperationUserName = bdata.RealName;
                        pbdata.OperationTime = DateTime.Now;
                        pbdata.Type = 2;
                        pbdata.OldOrNew = 1;
                        pbdata.PrinterId = blackScanningDto.MaterialPrinterId;
                        pbdetailed.Add(pbdata);

                        sql += $" UPDATE PB_ProductionOrderDetail set   CompleteNums=IFNULL(CompleteNums,0)+{item.StatisticalQuantity} WHERE Id='{item.OrderDetailId}' ; ";
                    }
                    else
                    {
                        if (pb_mater.IsNumber == true)
                        {
                            for (int i = 0; i < item.StatisticalQuantity; i++)
                            {
                                PrintQueue pq = new PrintQueue();
                                PB_JobBookingQrCode jkcode = new PB_JobBookingQrCode();

                                string jobcodeId = IdHelper.GetId();   //成品档案表ID
                                pq.Id = IdHelper.GetId();
                                pq.Name = pb_mater.Name;
                                pq.Date = DateTime.Now.ToString("yyyy-MM-dd");
                                pq.Dept = pb_mater.IsMine == true ? deptdata.DeptName : "";
                                if (RedisSetHelper.Exists("QualityTestingNumber"))
                                {
                                    string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                                    if (redis.GetString("QualityTestingTime") == thedate)
                                    {
                                        redis.SetString("QualityTestingNumber", (Convert.ToInt32(redis.GetString("QualityTestingNumber")) + 1).ToString());
                                        pq.No = jobcodeId + "," + redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                        jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                    }
                                    else
                                    {
                                        redis.SetString("QualityTestingTime", thedate);
                                        redis.SetString("QualityTestingNumber", "1");
                                        pq.No = jobcodeId + "," + redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                        jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                    }
                                }
                                else
                                {
                                    redis.SetString("QualityTestingNumber", "0");
                                }
                                pq.QrCode = h_FinishedProduct + "," + jobcodeId;
                                pq.TypeName = pb_mater.Spec;
                                pq.Type = Skill_Type.Statistics.ToString();
                                pq.Status = "0";
                                pq.Printer = blackScanningDto.MaterialPrinterId;
                                pq.IsProduct = pb_mater.IsProduct;
                                pq.BatchNumber = strguid;
                                pq.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                                pq.JobCount = item.StatisticalQuantity;
                                listdata.Add(pq);
                                jkcode.Id = jobcodeId;
                                jkcode.ParentId = jobId;
                                jkcode.QrCode = h_FinishedProduct + "," + jobcodeId;
                                jkcode.MaterialId = item.MaterialId;
                                jkcode.MaterialName = pb_mater.Name;
                                jkcode.OldOrNew = 1;
                                jkcode.RecId = Receivingid;
                                listcode.Add(jkcode);
                            }
                            PB_JobBookingDetailed pbdata = new PB_JobBookingDetailed();
                            pbdata.Id = IdHelper.GetId();
                            pbdata.No = "";
                            pbdata.Nums = item.StatisticalQuantity;
                            pbdata.DeptId = deptdata.DetpId;
                            pbdata.OperationUserId = _operator.UserId;
                            pbdata.OperationUserName = bdata.RealName;
                            pbdata.OperationTime = DateTime.Now;
                            pbdata.Type = 2;
                            pbdata.OldOrNew = 0;
                            pbdata.PrinterId = blackScanningDto.MaterialPrinterId;
                            pbdetailed.Add(pbdata);
                        }
                        else
                        {
                            PrintQueue pq = new PrintQueue();
                            PB_JobBookingQrCode jkcode = new PB_JobBookingQrCode();
                            string jobcodeId = IdHelper.GetId();   //成品档案表ID
                            pq.Id = IdHelper.GetId();
                            pq.Name = pb_mater.Name;
                            pq.Date = DateTime.Now.ToString("yyyy-MM-dd");
                            pq.Dept = pb_mater.IsMine == true ? deptdata.DeptName : "";
                            if (RedisSetHelper.Exists("QualityTestingNumber"))
                            {
                                string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                                if (redis.GetString("QualityTestingTime") == thedate)
                                {
                                    redis.SetString("QualityTestingNumber", (Convert.ToInt32(redis.GetString("QualityTestingNumber")) + 1).ToString());
                                    pq.No = jobcodeId + "," + redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                    jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                }
                                else
                                {
                                    redis.SetString("QualityTestingTime", thedate);
                                    redis.SetString("QualityTestingNumber", "1");
                                    pq.No = jobcodeId + "," + redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                    jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                }
                            }
                            else
                            {
                                redis.SetString("QualityTestingNumber", "0");
                            }
                            pq.QrCode = h_FinishedProduct + "," + jobcodeId;
                            pq.TypeName = pb_mater.Spec;
                            pq.Type = Skill_Type.Statistics.ToString();
                            pq.Status = "0";
                            pq.Printer = blackScanningDto.MaterialPrinterId;
                            pq.IsProduct = pb_mater.IsProduct;
                            pq.BatchNumber = strguid;
                            pq.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                            pq.JobCount = 1;
                            listdata.Add(pq);
                            jkcode.Id = jobcodeId;
                            jkcode.ParentId = jobId;
                            jkcode.QrCode = h_FinishedProduct + "," + jobcodeId;
                            jkcode.MaterialId = item.MaterialId;
                            jkcode.MaterialName = pb_mater.Name;
                            jkcode.OldOrNew = 1;
                            jkcode.RecId = Receivingid;
                            listcode.Add(jkcode);
                            PB_JobBookingDetailed pbdata = new PB_JobBookingDetailed();
                            pbdata.Id = IdHelper.GetId();
                            pbdata.No = "";
                            pbdata.Nums = 1;
                            pbdata.DeptId = deptdata.DetpId;
                            pbdata.OperationUserId = _operator.UserId;
                            pbdata.OperationUserName = bdata.RealName;
                            pbdata.OperationTime = DateTime.Now;
                            pbdata.Type = 2;
                            pbdata.OldOrNew = 1;
                            pbdata.PrinterId = blackScanningDto.MaterialPrinterId;
                            pbdetailed.Add(pbdata);
                        }
                    }
                }
            }
            else //判断是否是是自动打包，是
            {
                foreach (var item in list)
                {

                    memo += item.ProductName + ",";
                    memo = memo.Substring(0, memo.Length - 1);
                    var pb_mater = await Db.GetIQueryable<PB_Material>().Where(m => m.Id == item.MaterialId).FirstOrDefaultAsync();
                    //推送队列物料批次
                    strguid = Guid.NewGuid().ToString("N");

                    //推送队列包批次
                    packguid = Guid.NewGuid().ToString("N");

                    //定义是否是大小包
                    bool bltrueorfalse = false;
                    //计算自动打包的数量
                    decimal d = 0;
                    if (blackScanningDto.IsMax == true)
                    {
                        if (pb_mater.IsSpec == true)
                        {
                            if (pb_mater.MaxPackageNum == 0 || pb_mater.MaxPackageNum.IsNullOrEmpty())
                            {
                                throw new BusException("请维护" + pb_mater.Name + "的大包装数量!");
                            }
                            d = Math.Ceiling(Convert.ToDecimal(item.StatisticalQuantity) / Convert.ToDecimal(pb_mater.MaxPackageNum));
                            bltrueorfalse = true;
                        }
                        else
                        {
                            if (pb_mater.PackageNum == 0 || pb_mater.PackageNum.IsNullOrEmpty())
                            {
                                throw new BusException("请维护" + pb_mater.Name + "的小包装数量!");
                            }
                            d = Math.Ceiling(Convert.ToDecimal(item.StatisticalQuantity) / Convert.ToDecimal(pb_mater.PackageNum));
                        }
                    }
                    else
                    {
                        if (pb_mater.PackageNum == 0 || pb_mater.PackageNum.IsNullOrEmpty())
                        {
                            throw new BusException("请维护" + pb_mater.Name + "的小包装数量!");
                        }
                        d = Math.Ceiling(Convert.ToDecimal(item.StatisticalQuantity) / Convert.ToDecimal(pb_mater.PackageNum));
                    }
                    //是否是序列化管理
                    if (pb_mater.IsNumber == true)
                    {

                        double? worknums = Convert.ToDouble(item.StatisticalQuantity) + Convert.ToDouble(d);
                        double dcount = 0;
                        double dindex = 0;
                        //循环要生成的包数量
                        for (int i = 0; i < d; i++)
                        {
                            PrintQueue printdata = new PrintQueue();
                            PB_PackList packdata = new PB_PackList();
                            string dataName = "bag_" + timestamp + "_" + (i + 1);
                            string dataId = IdHelper.GetId();
                            dcount = bltrueorfalse == true ? Convert.ToDouble(pb_mater.MaxPackageNum) * (i + 1) : Convert.ToDouble(pb_mater.PackageNum) * (i + 1);
                            if (dcount <= item.StatisticalQuantity)
                            {
                                dindex = bltrueorfalse == true ? Convert.ToDouble(pb_mater.MaxPackageNum) : Convert.ToDouble(pb_mater.PackageNum);
                            }
                            else
                            {
                                dindex = bltrueorfalse == true ? (item.StatisticalQuantity - Convert.ToDouble(pb_mater.MaxPackageNum) * i) : (item.StatisticalQuantity - Convert.ToDouble(pb_mater.PackageNum) * i);
                            }
                            for (int j = 0; j < dindex; j++)
                            {
                                if (j == 0)
                                {
                                    packdata.BoxName = pb_mater.Name;
                                    packdata.MaterialId = pb_mater.Id;
                                    printdata.Name = pb_mater.Name;
                                    printdata.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                                    printdata.IsProduct = pb_mater.IsProduct;
                                }
                                PB_JobBookingQrCode jkcode = new PB_JobBookingQrCode();
                                string jobcodeId = IdHelper.GetId();   //成品档案表ID
                                if (RedisSetHelper.Exists("QualityTestingNumber"))
                                {
                                    string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                                    if (redis.GetString("QualityTestingTime") == thedate)
                                    {
                                        redis.SetString("QualityTestingNumber", (Convert.ToInt32(redis.GetString("QualityTestingNumber")) + 1).ToString());
                                        jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                    }
                                    else
                                    {
                                        redis.SetString("QualityTestingTime", thedate);
                                        redis.SetString("QualityTestingNumber", "1");
                                        jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                    }
                                }
                                else
                                {
                                    redis.SetString("QualityTestingNumber", "0");
                                }
                                jkcode.Id = jobcodeId;
                                jkcode.QrCode = h_FinishedProduct + "," + jobcodeId;
                                jkcode.MaterialId = item.MaterialId;
                                jkcode.MaterialName = pb_mater.Name;
                                jkcode.OldOrNew = 1;
                                jkcode.RecId = Receivingid;
                                jkcode.PackId = dataId;
                                listcode.Add(jkcode);

                                //包明细
                                PB_PackListInfo infodata = new PB_PackListInfo();
                                infodata.Id = IdHelper.GetId();
                                infodata.PackId = dataId;
                                infodata.JobBookingId = jobcodeId;
                                infodata.CreatorId = _operator.UserId;
                                infodata.CreatorName = bdata.RealName;
                                infodata.CreatorTime = DateTime.Now;
                                infodata.Deleted = false;
                                infodata.OldOrNew = 1;
                                listinfo.Add(infodata);
                                //成品码推送
                                PrintQueue materprint = new PrintQueue();
                                materprint.Id = IdHelper.GetId();
                                materprint.No = jobcodeId;
                                materprint.Name = pb_mater.Name;
                                materprint.TypeName = pb_mater.Spec;
                                materprint.Date = DateTime.Now.ToString("yyyy-MM-dd");
                                materprint.Dept = deptdata.DeptName;
                                materprint.QrCode = h_FinishedProduct + "," + jobcodeId;
                                materprint.Type = Skill_Type.Statistics.ToString();
                                materprint.Status = "0";
                                materprint.Printer = blackScanningDto.MaterialPrinterId;
                                materprint.IsProduct = pb_mater.IsProduct;
                                materprint.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                                materprint.BatchNumber = strguid;
                                materprint.JobCount = 2;
                                listdata.Add(materprint);
                            }


                            //包信息
                            packdata.Id = dataId;
                            packdata.DeptId = bdata.DepartmentId;
                            packdata.BoxNo = dataName;
                            packdata.Status = 2;
                            packdata.Type = 1;
                            packdata.State = true;
                            packdata.CreateTime = DateTime.Now;
                            packdata.CreatorId = _operator.UserId;
                            packdata.Deleted = false;
                            packdata.CreatorName = bdata.RealName;
                            packdata.Nums = dindex;
                            packdata.QrCode = h_Pack + "," + dataId;
                            packdata.WorkshopId = bdata.DepartmentId;
                            packdata.OldOrNew = 1;
                            packdata.PackageType = 0;
                            packdata.IsUnpacking = dindex == pb_mater.PackageNum ? false : true;
                            listpack.Add(packdata);

                            //推送包信息到打印队列
                            printdata.Id = IdHelper.GetId();
                            printdata.No = dataId;
                            printdata.Date = DateTime.Now.ToString("yyyy-MM-dd");
                            printdata.Dept = pb_mater.IsMine == true ? deptdata.DeptName : "";
                            printdata.BatchNumber = packguid;
                            printdata.QrCode = h_Pack + "," + dataId;
                            printdata.Type = Skill_Type.Package.ToString();
                            printdata.Status = dindex.ToString();
                            printdata.Printer = blackScanningDto.PrinterId;
                            printdata.TypeName = pb_mater.Spec;
                            printdata.JobCount = Convert.ToDouble(d);
                            listdata.Add(printdata);
                        }

                        //生成成品记录
                        PB_JobBookingDetailed pbdata = new PB_JobBookingDetailed();
                        pbdata.Id = IdHelper.GetId();
                        pbdata.No = item.OrderId;
                        pbdata.Nums = item.StatisticalQuantity;
                        pbdata.DeptId = deptdata.DetpId;
                        pbdata.OperationUserId = _operator.UserId;
                        pbdata.OperationUserName = bdata.RealName;
                        pbdata.OperationTime = DateTime.Now;
                        pbdata.Type = 4;
                        pbdata.OldOrNew = 1;
                        pbdata.PrinterId = blackScanningDto.PrinterId;
                        pbdetailed.Add(pbdata);
                    }
                    else
                    {
                        //TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
                        //string sjtime = Convert.ToInt64(ts.TotalSeconds).ToString();
                        double dcount = 0;
                        double dindex = 0;
                        //循环要生成的包数量
                        for (int i = 0; i < d; i++)
                        {
                            dcount = Convert.ToDouble(pb_mater.PackageNum) * (i + 1);
                            if (dcount <= item.StatisticalQuantity)
                            {
                                dindex = Convert.ToDouble(pb_mater.PackageNum);
                            }
                            else
                            {
                                dindex = item.StatisticalQuantity - Convert.ToDouble(pb_mater.PackageNum) * i;
                            }
                            PB_JobBookingQrCode jkcode = new PB_JobBookingQrCode();
                            string dataId = IdHelper.GetId();
                            string jobcodeId = IdHelper.GetId();   //成品档案表ID
                            if (RedisSetHelper.Exists("QualityTestingNumber"))
                            {
                                string thedate = DateTime.Now.ToString("yyyy-MM-dd");
                                if (redis.GetString("QualityTestingTime") == thedate)
                                {
                                    redis.SetString("QualityTestingNumber", (Convert.ToInt32(redis.GetString("QualityTestingNumber")) + 1).ToString());
                                    jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                }
                                else
                                {
                                    redis.SetString("QualityTestingTime", thedate);
                                    redis.SetString("QualityTestingNumber", "1");
                                    jkcode.SerialNumber = redis.GetString("QualityTestingNumber").PadLeft(5, '0');
                                }
                            }
                            else
                            {
                                redis.SetString("QualityTestingNumber", "0");
                            }
                            jkcode.Id = jobcodeId;
                            jkcode.QrCode = h_FinishedProduct + "," + jobcodeId;
                            jkcode.MaterialId = item.MaterialId;
                            jkcode.MaterialName = pb_mater.Name;
                            jkcode.OldOrNew = 1;
                            jkcode.RecId = Receivingid;
                            jkcode.PackId = dataId;
                            listcode.Add(jkcode);

                            //成品码推送
                            PrintQueue materprint = new PrintQueue();
                            materprint.Id = IdHelper.GetId();
                            materprint.No = jobcodeId;
                            materprint.Name = pb_mater.Name;
                            materprint.TypeName = pb_mater.Spec;
                            materprint.Date = DateTime.Now.ToString("yyyy-MM-dd");
                            materprint.Dept = deptdata.DeptName;
                            materprint.QrCode = h_FinishedProduct + "," + jobcodeId;
                            materprint.Type = Skill_Type.Statistics.ToString();
                            materprint.Status = "0";
                            materprint.Printer = blackScanningDto.MaterialPrinterId;
                            materprint.IsProduct = pb_mater.IsProduct;
                            materprint.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                            materprint.BatchNumber = strguid;
                            materprint.JobCount = 2;
                            listdata.Add(materprint);


                            //生成包信息
                            PB_PackList packdata = new PB_PackList();
                            string dataName = "bag_" + timestamp + "_" + (i + 1);

                            packdata.Id = dataId;
                            packdata.DeptId = bdata.DepartmentId;
                            packdata.BoxNo = dataName;
                            packdata.BoxName = pb_mater.Name;
                            packdata.MaterialId = item.MaterialId;
                            packdata.Nums = dindex;
                            packdata.State = false;
                            packdata.QrCode = h_Pack + "," + dataId;
                            packdata.Type = 1;
                            packdata.CreatorId = _operator.UserId;
                            packdata.CreateTime = DateTime.Now;
                            packdata.CreatorName = bdata.RealName;
                            packdata.WorkshopId = bdata.DepartmentId;
                            packdata.Status = 2;
                            packdata.OldOrNew = 1;
                            packdata.PackageType = 0;
                            packdata.NoticeId = NoticeId;
                            packdata.IsUnpacking = dindex == pb_mater.PackageNum ? false : true;
                            listpack.Add(packdata);
                            //生成包明细
                            PB_PackListInfo infodata = new PB_PackListInfo();
                            infodata.Id = IdHelper.GetId();
                            infodata.PackId = dataId;
                            infodata.JobBookingId = jobcodeId;
                            infodata.CreatorId = _operator.UserId;
                            infodata.CreatorName = bdata.RealName;
                            infodata.CreatorTime = DateTime.Now;
                            infodata.Deleted = false;
                            infodata.OldOrNew = 1;
                            listinfo.Add(infodata);

                            //包码推送
                            PrintQueue printdata = new PrintQueue();
                            printdata.Id = IdHelper.GetId();
                            printdata.No = dataId;
                            printdata.Name = pb_mater.Name;
                            printdata.Date = DateTime.Now.ToString("yyyy-MM-dd");
                            printdata.Dept = pb_mater.IsMine == true ? deptdata.DeptName : "";
                            printdata.QrCode = h_Pack + "," + dataId;
                            printdata.Type = Skill_Type.Package.ToString();
                            printdata.Status = dindex.ToString();
                            printdata.Printer = blackScanningDto.PrinterId;
                            printdata.TypeName = pb_mater.Spec;
                            printdata.BatchNumber = packguid;
                            printdata.Code = pb_mater.IsProduct == true ? pb_mater.ProductCode : pb_mater.Code;
                            printdata.IsProduct = pb_mater.IsProduct;
                            printdata.JobCount = Convert.ToDouble(d);
                            listdata.Add(printdata);
                        }
                        //添加mongo成品日志
                        PB_JobBookingDetailed pbdata = new PB_JobBookingDetailed();
                        pbdata.Id = IdHelper.GetId();
                        pbdata.No = item.OrderId;
                        pbdata.Nums = Convert.ToDouble(d);
                        pbdata.DeptId = deptdata.DetpId;
                        pbdata.OperationUserId = _operator.UserId;
                        pbdata.OperationUserName = bdata.RealName;
                        pbdata.OperationTime = DateTime.Now;
                        pbdata.Type = 4;
                        pbdata.OldOrNew = 1;
                        pbdata.PrinterId = blackScanningDto.PrinterId;
                        pbdetailed.Add(pbdata);
                    }
                    sql += $" UPDATE PB_ProductionOrderDetail set   CompleteNums=IFNULL(CompleteNums,0)+{item.StatisticalQuantity} WHERE Id='{item.OrderDetailId}' ; ";
                }

            }
            MongoDBHelper mongo = new MongoDBHelper(_mongodbOptions);
            listdata.GroupBy(x => new { x.Id, x.No, x.Name, x.TypeName, x.Date, x.Dept, x.QrCode, x.Type, x.Status, x.Printer }).Select(x => new
            {
                Id = x.Key.Id,
                No = x.Key.No,
                Name = x.Key.Name,
                TypeName = x.Key.TypeName,
                Date = x.Key.Date,
                Dept = x.Key.Dept,
                QrCode = x.Key.QrCode,
                Type = x.Key.Type,
                Status = x.Key.Status,
                Printer = x.Key.Printer
            }).ToList();

            //记录总数
            double Nums = 0;
            double? TotalAmt = 0;
            double? AmtNums = 0;
            double? SumAmtNums = 0;
            string RecId = string.Empty;
            foreach (var item in list)
            {
                Nums += item.StatisticalQuantity;
                var materail = await Db.GetIQueryable<PB_Material>().Where(m => m.Id == item.MaterialId).FirstOrDefaultAsync();
                if (materail != null)
                {
                    TotalAmt += materail.Price == null ? 0 : materail.Price;
                }
            }
            //创建收货明细
            List<TD_RecDetail> TD_RecDetails = new List<TD_RecDetail>();
            //汇总获取流转单物料信息
            var recdetail = list.GroupBy(s => new { s.MaterialId }).Select(x => new {
                MaterialId = x.Key.MaterialId,
                StatisticalQuantity = x.Sum(i => i.StatisticalQuantity)
            }).ToList();
            //遍历明细物料信息
            foreach (var item in recdetail)
            {
                TD_RecDetail tD_RecDetail = new TD_RecDetail();
                tD_RecDetail.Id = IdHelper.GetId();
                tD_RecDetail.RecId = Receivingid;
                tD_RecDetail.StorId = blackScanningDto.StorId;
                tD_RecDetail.LocaId = String.Empty;
                tD_RecDetail.MaterialId = item.MaterialId;
                var materail = await Db.GetIQueryable<PB_Material>().Where(m => m.Id == item.MaterialId).FirstOrDefaultAsync();
                if (materail != null)
                {
                    AmtNums = materail.Price == null ? 0 : materail.Price;
                }
                tD_RecDetail.Price = Convert.ToDouble(AmtNums);
                tD_RecDetail.PlanNum = item.StatisticalQuantity;
                tD_RecDetail.RecNum = item.StatisticalQuantity;
                tD_RecDetail.InNum = 0;
                if (materail != null)
                {
                    SumAmtNums += materail.Price == null ? 0 : materail.Price * item.StatisticalQuantity;
                }
                tD_RecDetail.Amount = Convert.ToDouble(SumAmtNums);
                tD_RecDetail.CreateTime = DateTime.Now;
                tD_RecDetail.CreatorId = _operator.UserId;
                tD_RecDetail.Deleted = false;
                tD_RecDetail.TaskId = "";
                tD_RecDetail.TaxRate = 0;
                tD_RecDetail.CirBillId = "";
                TD_RecDetails.Add(tD_RecDetail);
            }



            var memodata = blackScanningDto.detail.GroupBy(x => new { x.MaterialId }).Select(x => new { MaterialId = x.Key.MaterialId }).ToList();

            //生成入库通知单
            PB_WarehousingNotice pB_WarehousingNotice = new PB_WarehousingNotice();
            pB_WarehousingNotice.Id = IdHelper.GetId();
            pB_WarehousingNotice.QrCode = "18," + pB_WarehousingNotice.Id;
            pB_WarehousingNotice.DeptId = _operator.Property.DepartmentId;
            pB_WarehousingNotice.CreatorName = base_user.RealName;
            pB_WarehousingNotice.CreateTime = DateTime.Now;
            pB_WarehousingNotice.CreatorId = _operator.UserId;
            pB_WarehousingNotice.Deleted = false;
            pB_WarehousingNotice.Type = 1;
            pB_WarehousingNotice.ReserveState = 0;
            pB_WarehousingNotice.ReserveParent = "";
            pB_WarehousingNotice.ParentId = Receivingid;
            pB_WarehousingNotice.Memo = memo;
            pB_WarehousingNotice.ReserveStyleName = string.Empty;
            pB_WarehousingNotice.ReserveDeliveryTime = DateTime.Now;
            pB_WarehousingNotice.CusID = "";
            pB_WarehousingNotice.CustomerAddress = string.Empty;
            pB_WarehousingNotice.CustomerToexamine = 1;
            pB_WarehousingNotice.StorId = blackScanningDto.StorId;
            //生成收货单
            TD_Receiving tD_Receiving = new TD_Receiving();
            tD_Receiving.Id = Receivingid;
            RecId = Receivingid;
            tD_Receiving.StorId = blackScanningDto.StorId;
            tD_Receiving.Code = IdHelper.GetId();
            tD_Receiving.OrderTime = DateTime.Now;
            tD_Receiving.RecTime = DateTime.Now;
            tD_Receiving.Type = "Product";
            tD_Receiving.RefCode = pB_WarehousingNotice.Id;
            tD_Receiving.Status = 3;
            tD_Receiving.SupId = "";
            tD_Receiving.TotalNum = Nums;
            tD_Receiving.InNum = 0;
            tD_Receiving.TotalAmt = Convert.ToDouble(TotalAmt);
            tD_Receiving.Remarks = "";
            tD_Receiving.ConfirmUserId = "";
            tD_Receiving.ConfirmTime = null;
            tD_Receiving.AuditUserId = "";
            tD_Receiving.AuditeTime = null;
            tD_Receiving.CreateTime = DateTime.Now;
            tD_Receiving.CreatorId = _operator.UserId;
            tD_Receiving.Deleted = false;
            tD_Receiving.QrCode = "23," + Receivingid;
            tD_Receiving.CusID = "";
            tD_Receiving.DeptId = _operator.Property.DepartmentId;
            tD_Receiving.UpdateTime = DateTime.Now;
            tD_Receiving.OrderId = OrderId;
            tD_Receiving.ToReviewId = null;
            tD_Receiving.ToReviewTime = null;
            tD_Receiving.Status = 3;

            //插入成品报工
            PB_JobBooking pB_JobBooking = new PB_JobBooking();

            string sqllist = "select * from Base_Device where ProcecureSubId = (select Id FROM PB_ProcecureSub WHERE Name LIKE'%上件%')";
            List<Base_Device> devicelist = await Db.GetListBySqlAsync<Base_Device>(sqllist);

            pB_JobBooking.Id = jobId;
            pB_JobBooking.ParentId = blackScanningDto.detail[0].OrderDetailId;
            pB_JobBooking.Nums = Nums;//
            pB_JobBooking.ReturnNums = 0;
            pB_JobBooking.Reason = "";
            if (devicelist.Count > 0)
            {
                pB_JobBooking.DeviceId = devicelist[0].Id;
            }
            else
            {
                pB_JobBooking.DeviceId = "";
            }

            pB_JobBooking.DeptId = base_user.DepartmentId;
            pB_JobBooking.ReportUserId = _operator.UserId;
            pB_JobBooking.ReportUserName = base_user.RealName;
            pB_JobBooking.ReportTime = DateTime.Now;
            pB_JobBooking.QualityUserId = "";
            pB_JobBooking.QualityUserName = "";
            pB_JobBooking.QualityTime = null;
            pB_JobBooking.StatisticsUserId = null;
            pB_JobBooking.StatisticsUserName = null;
            pB_JobBooking.StatisticsTime = null;
            pB_JobBooking.StatisticsNums = 0;
            pB_JobBooking.TakeOverUserId = null;
            pB_JobBooking.TakeOverUserName = null;
            pB_JobBooking.TakeOverTime = null;
            pB_JobBooking.CreateTime = DateTime.Now;
            pB_JobBooking.JobType = 3;//黑件报工
            pB_JobBooking.CreatorId = _operator.UserId;
            pB_JobBooking.CreatorRealName = base_user.RealName;
            pB_JobBooking.MaterialId = blackScanningDto.detail[0].MaterialId;
            pB_JobBooking.MaterialName = "";
            pB_JobBooking.WorkshopId = base_user.DepartmentId;
            pB_JobBooking.OldOrNew = 1;
            pB_JobBooking.CirBillId = blackScanningDto.detail[0].BillId;
            pB_JobBooking.DispatchDetailId = "";
            pB_JobBooking.Remarks = "";

            //插入收货单明细
            await Db.InsertAsync<TD_RecDetail>(TD_RecDetails);
            //插入入库通知单
            await Db.InsertAsync<PB_WarehousingNotice>(pB_WarehousingNotice);
            await Db.InsertAsync<TD_Receiving>(tD_Receiving);
            //插入mongo队列
            await mongo.InsertManyAsync<PrintQueue>(PrintQueueCollection, listdata);
            //插入mongo报工操作明细
            await mongo.InsertManyAsync<PB_JobBookingDetailed>(JobBookingOperation, pbdetailed);
            //插入包明细
            await Db.InsertAsync<PB_PackListInfo>(listinfo);
            //插入包信息
            await Db.InsertAsync<PB_PackList>(listpack);
            //统计完插入报工明细表二维码信息
            await Db.InsertAsync(listcode);
            await Db.InsertAsync<PB_JobBooking>(pB_JobBooking);
            //反写生产任务单已完成数量
            await Db.ExecuteSqlAsync(sql);



            return RecId;

        }

        #endregion

    }
}